SOLVED

help with vlookup (index small) to show nth row when one of 2 criteria match

New Contributor

i am using the below formula to identify the cell O5 (nth) match =INDEX($I$8:$I$801,SMALL(IF($L5=$C$8:$C$801,ROW($C$8:$C$801)-ROW($C$8)+1),O5))

The issue is i want to edit this formula in a way that has $L5 or $L6 as a match.  This would then lookup to the nth item matching either of 2 descriptions rather than just one.  is this possible?

4 Replies

@lliberm1 

=INDEX($I$8:$I$801,SMALL(IF(($L5=$C$8:$C$801)+($L6=$C$8:$C$801),ROW($C$1:$C$792)-ROW($C$1)+1),O5))

Is this what you are looking for? Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021. 

best response confirmed by lliberm1 (New Contributor)

@Quadruple_Pawn If one would be using 365 or 2021, why would you use such a complicated function at all? Wouldn't it be easier to use FILTER, for example like this:

=INDEX(FILTER(I8:I801,(C8:C801=L5)+(C8:C801=L6)),O5)

 

@Riny_van_Eekelen 

Thank you for sharing the INDEX(FILTER formula which works perfectly. In this post, the formula was already predefined in the introductory question. It wasn't difficult to insert a second criterionIf everyone involved works with Office365, there is practically no reason to work without functions such as FILTER, LET, SWITCH, SORT which simplify many tasks.