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.
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?
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 "".
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.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
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
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
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.