# Returning 2nd match in IFERROR(INDEX formula

#### cappers

##### New Member
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.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### AhoyNC

##### Well-known Member
Do you just want the 2nd match or all matches?

#### cappers

##### New Member
Just the second match

#### AhoyNC

##### Well-known Member
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

Code:
``=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

##### New Member
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

##### New Member
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

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

#### AhoyNC

##### Well-known Member
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 "".

Replies
5
Views
724
Replies
2
Views
364
Replies
4
Views
650
Replies
7
Views
497
Replies
8
Views
6K

1,195,748
Messages
6,011,426
Members
441,614
Latest member
TiaGtz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back