SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-3185859%22%20slang%3D%22en-US%22%3Ehelp%20with%20vlookup%20(index%20small)%20to%20show%20nth%20row%20when%20one%20of%202%20criteria%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3185859%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20am%20using%20the%20below%20formula%20to%20identify%20the%20cell%20O5%20(nth)%20match%20%3DINDEX(%24I%248%3A%24I%24801%2CSMALL(IF(%24L5%3D%24C%248%3A%24C%24801%2CROW(%24C%248%3A%24C%24801)-ROW(%24C%248)%2B1)%2CO5))%3C%2FP%3E%3CP%3EThe%20issue%20is%20i%20want%20to%20edit%20this%20formula%20in%20a%20way%20that%20has%20%24L5%20or%20%24L6%20as%20a%20match.%26nbsp%3B%20This%20would%20then%20lookup%20to%20the%20nth%20item%20matching%20either%20of%202%20descriptions%20rather%20than%20just%20one.%26nbsp%3B%20is%20this%20possible%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3185859%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3185979%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20vlookup%20(index%20small)%20to%20show%20nth%20row%20when%20one%20of%202%20criteria%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3185979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1311725%22%20target%3D%22_blank%22%3E%40lliberm1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DINDEX(%24I%248%3A%24I%24801%2CSMALL(IF((%24L5%3D%24C%248%3A%24C%24801)%2B(%24L6%3D%24C%248%3A%24C%24801)%2CROW(%24C%241%3A%24C%24792)-ROW(%24C%241)%2B1)%2CO5))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%20Enter%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3186125%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20vlookup%20(index%20small)%20to%20show%20nth%20row%20when%20one%20of%202%20criteria%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3186125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eperfect%20thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3187022%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20vlookup%20(index%20small)%20to%20show%20nth%20row%20when%20one%20of%202%20criteria%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3187022%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3BIf%20one%20would%20be%20using%20365%20or%202021%2C%20why%20would%20you%20use%20such%20a%20complicated%20function%20at%20all%3F%20Wouldn't%20it%20be%20easier%20to%20use%20FILTER%2C%20for%20example%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(FILTER(I8%3AI801%2C(C8%3AC801%3DL5)%2B(C8%3AC801%3DL6))%2CO5)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.