Forum Discussion
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?
perfect thanks
4 Replies
=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.
OliverScheurich 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)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 criterion. If everyone involved works with Office365, there is practically no reason to work without functions such as FILTER, LET, SWITCH, SORT which simplify many tasks.
perfect thanks
