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

Question

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

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.

Attempt 1

Attempt 1(2)

Ricardo Diaz · Accepted Answer · 2019-02-04 20:53:20Z

1

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:

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))

answered Feb 4, 2019 at 20:53

Ricardo Diaz

5,7262 gold badges24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Matt Picca Over a year ago

I did exactly what you said and merged the two columns and copied your formula, but it is giving me a #N/A result

Ricardo Diaz Over a year ago

Can you take a screenshot of both screens?

Matt Picca Over a year ago

I added the screen shots to the body under: Attempt 1 & Attempt 1(2), the formula for Waterfall tab is blocking the result from H1086, but it says #N/A

Ricardo Diaz Over a year ago

Recheck the merged column formula. Look at my image

Ricardo Diaz Over a year ago

Also, is the date column, a date value or text that looks like a date?

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related