Need excel formula to match one value on second sheet for Multiple rows

0
votes

I have two excel sheets one with this image of sheet2 enter image description here

on which I need to get the matching rows from sheet1 to match x from column C and show all data on sheet2. Sheet1 image is

enter image description here

I am using this formula

=INDEX(sheet1!$D:$J,MATCH("x", sheet1!$C:$C,0), MATCH(B$2, sheet1!$B$5:$J$5,0))

but it only gets the first x row not other rows and i want to add rows to sheet2 whenever sheet1 column C contains x value so sheet2 automatically updates with a new row inserted in sheet1.

1
excelexcel-formula
So what is your question? – Scott Craner
I am not able to get second and third row result, even i will have more rows with x value in column C so how can i get all match rows on sheet2.Please read the details i have mentioned my question in details – Hassaan
That is a statement not a question. Ask a specific question. Your title says formula your tags say vba, which is it? – Scott Craner
Sir I have mentioned excel and also vba, if this is to be done with excel formula or VBA , i am ok with both...but i am using excel formula but unable to get the multiple row result, the formula i have mentioned is only getting only one row result from C7 of sheet1 but what i need is to get more results with value of x on column C , like on sheet1 i have C7 , C8, C9 with value of x then I wants to get C8 and C9 result as well and if i will have any other row with value of x then i wants that result also to get on sheet2. – Hassaan
@YowE3K can you please tell me which code can i paste into the question ?? – Hassaan

1 Answers

1
votes

This array formula, means you need to click Ctrl + Shift + Enter together, should work for you based on your set up. But you will need to adjust the ranges and other parameters accordingly. Enter this formula into cell B4 and drag/copy to cover all the fields you need.

=IF(COUNTIF(Sheet1!$C$1:$C$14,"x")-COUNTA(B$3:B3)<=0,"",IFERROR(INDEX(Sheet1!$D$1:$J$14,SMALL(IF(Sheet1!$C$1:$C$14="x",ROW(Sheet1!$C$1:$C$14)+COUNTA(B$3:B3)),1),MATCH(Sheet1!D$5,Sheet1!$D$5:$J$5,0)),""))

SMALL here is to find the row that you want to output. Basically this is used to replace your INDEX/MATCH so it can return multiple results.

One reason I didn't include the whole column or row is because this is an array formula which will slow down the performance. Only use the range you need or name ranges to make your life easier.