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(vloo kup(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

Hi!

Try this:

=IF(NOT(ISNA(VLOOKUP(A2,list1,2,0))),VLOOKUP(A2,li st1,2,0),IF(NOT(ISNA(VLOOKUP(A2,list2,2,0))),VLOOK UP(A2,list2,2,0),IF(NOT(ISNA(VLOOKUP(A2,list3,2,0) )),VLOOKUP(A2,list3,2,0),"")))

Biff

"Oggie Ben Doggie" wrote in message
oups.com...
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(vloo kup(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

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

=IF(ISERROR(VLOOKUP(A2,List1,2,FALSE))=FALSE,VLOOK UP(A2,List1,2,FALSE),IF(ISERROR(VLOOKUP(A2,List2,2 ,FALSE))=FALSE,VLOOKUP(A2,List2,2,FALSE),IF(ISERRO R(VLOOKUP(A2,List3,2,FALSE))=FALSE,VLOOKUP(A2,List 3,2,FALSE),"No Match")

HTH,
Elkar


"Oggie Ben Doggie" wrote:

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(vloo kup(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