Multiple Arrays, Vlookup

[Oggie Ben Doggie]

Hi all,

I have 3 arrays on 3 worksheets in an Excel file.

On the 4th worksheet, I have a list of values in column A.

I'm looking to compare the values in column A to the arrays defined on
the other 3 sheets, to pull the 2nd column value and other subsequent
columns.

I thought I could vlookup it, combining with an if and iserror like so:

=if(iserror(vlookup(a2,list1,2,false),iserror(vlookup(a2,list2,2,false),vlookup(a2,list3,2,false))))

It errors on me though. Tells me the expression is too complex.

The important thing is that I check across all three sheets, as below:
if (vlookup(a2,list1,2,false) errors then
if (vlookup(a2,list2,2,false) errors then (vlookup(a2,list3,2,false)
else "no match"

Is it possible to nest things to accomplish the above?

O.Ben.D

 

[Biff]

Hi!

Try this:

=IF(NOT(ISNA(VLOOKUP(A2,list1,2,0))),VLOOKUP(A2,list1,2,0),IF(NOT(ISNA(VLOOKUP(A2,list2,2,0))),VLOOKUP(A2,list2,2,0),IF(NOT(ISNA(VLOOKUP(A2,list3,2,0))),VLOOKUP(A2,list3,2,0),"")))

Biff

 

[Guest]

You were on the right track. Your formula just needs a little tweaking.

=IF(ISERROR(VLOOKUP(A2,List1,2,FALSE))=FALSE,VLOOKUP(A2,List1,2,FALSE),IF(ISERROR(VLOOKUP(A2,List2,2,FALSE))=FALSE,VLOOKUP(A2,List2,2,FALSE),IF(ISERROR(VLOOKUP(A2,List3,2,FALSE))=FALSE,VLOOKUP(A2,List3,2,FALSE),"No Match")

HTH,
Elkar