CODEWITHSUNDEEP
excelexcel-formula
Hassaan
Hassaan

Reputation: 328

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

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.

Upvotes: 0

Views: 154

Answers (1)

ian0411
ian0411

Reputation: 4265

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.

Upvotes: 1

Related Questions