Multiple Arrays, Vlookup


O

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
 
Ad

Advertisements

B

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
 
Ad

Advertisements

G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding multiple vlookups 3
if and vlookup formula 9
lookup on range (A2 and D2:D30) 1
VLOOKUP 1
vlookup 3 sheets 1
Vlookup and return sheet name also 2
Vlookup across 3 worksheets 2
VLOOKUP and MID Function 9

Top