 SOLVED

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

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

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

``=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)
Solution

perfect thanks

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

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

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

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.