Feb 18 2022 11:36 AM
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?
Feb 18 2022 11:51 AM
=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.
Feb 18 2022 12:43 PM
Solutionperfect thanks
Feb 18 2022 10:58 PM
@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)
Feb 19 2022 05:01 AM
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.
Feb 18 2022 12:43 PM
Solution