I'm trying to use IF statements to execute multiple vlookups, with the goal of searching multiple ranges in sequence for a single search key if the key is not found in the first range.

This is premised on the output of a Vlookup when the key is not found being "#N/A", and triggering the second vlookup when that is the output from the first range. My code:

=if(vlookup(A6,Range1!A:A,1,false)<>#N/A,"Match",if(vlookup(A6,Range2!A:A,1,false)<>#N/A,"Match",if(vlookup(A6,Range3!A:A,1,false)<>#N/A,"Match",if(vlookup(A6,Range4!A:A,1,false)<>#N/A,"Match",if(vlookup(A6,Range5!A:A,1,false)<>#N/A,"Match","Not")))))

What I was expecting is for the lookup to proceed through the ranges if an output of #N/A is found, ultimately resulting in each search key being assigned "Match" or "Not". What is happening is that the second If statement is not executing, and my outputs are either "Match" or "#N/A".

CodePudding user response：

try this out:

=if(NOT(ISERROR(vlookup(A6,Range1!B:B,1,false))),"Match",if(NOT(ISERROR(vlookup(A6,Range2!B:B,1,false))),"Match",if(NOT(ISERROR(vlookup(A6,Range3!B:B,1,false))),"Match",if(NOT(ISERROR(vlookup(A6,Range4!B:B,1,false))),"Match",if(NOT(ISERROR(vlookup(A6,Range5!B:B,1,false))),"Match","Not")))))

CodePudding user response：

I think this is a more compact representation that achieves the same result:

=arrayformula(if(sum(n(A6={Range1!B:B;Range1!B:B;Range3!B:B;Range3!B:B;Range4!B:B;Range5!B:B;Range6!B:B}))>=1,"Match","Not"))

The VLOOKUPs are not needed (as you're just looking for a match in a list rather than actually doing a lookup here), and you can union all the ranges together to eliminate a lot of unnecessary nesting.