Returning 2nd match in IFERROR(INDEX formula

[cappers]

Hi,

Here is my formula:

=IFERROR(INDEX(cost_L,MATCH(B72,MIX3_L,0)),0)/12*S72

The formula works great for the first match in MIX3, but I need it to return the 2nd match. Any help would be greatly appreciated.

 

[AhoyNC]

Do you just want the 2nd match or all matches?

 

[cappers]

Just the second match

 

[AhoyNC]

See if this works:

The second ROW function you need to enter the first cell in your cost_L range and lock it (example: if first cell in range is B3 then $B$3).
This is an array function and must be entered with CTRL-SHIFT-ENTER

=IFERROR(INDEX(cost_L,SMALL(IF(MIX3_L=B72,ROW(cost_L)-ROW([COLOR=#ff0000]THIS NEEDS TO BE THE FIRST CELL IN RANGE cost_L[/COLOR])+1),2))/12*S72,"")

Will there always be a second match? If not this formula will return a blank.

 

[cappers]

ADVERTISEMENT

So there is a second match, but it still returned blank? I tried it in row 115 with this
=IFERROR(INDEX(cost_L,SMALL(IF(MIX3_L=B115,ROW(cost_L)-ROW(Sheet3!$O$2)+1),2))/12*S115,"")
and I did it as an array. Can you please just take a peek and let me know if I did something wrong?

 

[cappers]

Quick clarification to my previous post... there is a 2nd match in the MIX column, not the cost column (if that makes a difference)

 

[cappers]

OH! Your formula worked!!! (It was the MIX column I needed, not MIX3). Thank you so much for your help!

 

[AhoyNC]

Glad you got it to work. Thanks for the feedback.
IF there's not a second match and you would want the first match returned you could put your first formula at the end of the IFERROR instaed of the "".