Excel Index Match with multiple criteria and multiple results - Return first result

0
votes

I am using index(match(match to find a value based on two different criteria. There are many results that will populate, I just want to return the first result. What do I need to add to my Index Match formula in order to return the first result that matches? Below is my code and a breakdown with images:

=INDEX(Master_Query[Current Balance On Hand],MATCH('Waterfall 2018'!$R$1086,Master_Query[Part Number],0),MATCH($BX$1,Master_Query[Date],0))

Cell H1086 is were i need the result to return. I need it to match the highlighted criteria: Part Number in cell R1086 and Date in cell BX1 enter image description here

This is the table that we get the results from, as you can see there are many results that match the criteria in the formula, i just want to return the first one since they are all the same. Note: The date column is filtered; there are multiple dates that will result in different "current balance on hand"(column D) results, thus I cannot use a vlookup formula. I just filtered it to make it easy to understand my problem. enter image description here

Attempt 1 enter image description here

Attempt 1(2) enter image description here

1
excelindexingmatchformula

1 Answers

1
votes

The second Match looks for the column number in the index formula. So it doesn't do what you want.

One alternative is to add a column to the ELX forecast table that merges the "Date" column and the "Part number"

The formula of that column would be:

enter image description here

After that, in your waterfall sheet, replace your current index/match formula with:

=INDEX(Master_Query[Current Balance On Hand],MATCH(BX$1 & $R1086,Master_Query[Merged column],0))