Changing the row reference when dragging down index match formula in excel

0
votes

I am trying to reference the formula below to pick up the next row when I drag it down my workbook so that the 1 becomes 2, 3, 4 etc. What is the most efficient way of doing this?

=(INDEX(Data!$A:$M,MATCH(1,(Data!$E:$E=$B$1)*(Data!$F:$F=$B$2),0)*1,3))

Thanks,

Richard

1
arraysexcelindexingmatch
Do you mean the first 1 in your formula or the second 1? – Tom Sharpe
The second one, the one that references the row at the end of the formula. – Richard
I’m trying to copy down a product code reference based on two dropdown data validation boxes so I need to match on two criteria and have the data change dynamically based on this. – Richard
I haven't worked out how multiplying the row by 1, 2 etc. is going to be useful. Do you want to get the first, second, third match etc. in your data? As always, showing some sample data and results in your question would be very helpful to people answering. – Tom Sharpe
Yes, I want the first, second, third match. – Richard

1 Answers

0
votes

Some variation on this formula should do it - the last parameter to Aggregate increases as you pull the formula down so you get the first, second, third...occurrence of the matching row.

=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$2:C$20)/((E$2:E$20=$B$1)*(F$2:F$20=$B$2)),ROW()-1)),"")

enter image description here