Vlookup in 2 lists

[Nav]

I have a list in col A and a list in col S. I want to
lookup a value (ACA) that if it is not found in col A then
it looks in col S and returns the adjacent col to it.

The data in col A and col S are text and are each unique.

Is there an easy way to do this?


Thanks in advance for any help/suggestions.

 

[Chip Pearson]

Nav,

Try a formula like the following. Change the A1:B10 and S1:T10
ranges to meet your needs.

=IF(ISNA(VLOOKUP("ACA",A1:B10,2,FALSE)),IF(ISNA(VLOOKUP("ACA",S1:
T10,2,FALSE)),"not found in A or
S",VLOOKUP("ACA",S1:T10,2,FALSE)),VLOOKUP("ACA",A1:B10,2,FALSE))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Peo Sjoblom]

=IF(ISNUMBER(MATCH("ACA",A2:A200,0)),VLOOKUP("ACA",A2:B200,2,0),VLOOKUP("ACA
",S2:T200,2,0))


or if it is possible the lookup value is not present at all

=IF(ISNUMBER(MATCH("ACA",A2:A200,0)),VLOOKUP("ACA",A2:B200,2,0),IF(ISNUMBER(
MATCH("ACA",S2:S200,0)),VLOOKUP("ACA",S2:T200,2,0),"Not Found At All"))

 

[Frank Kabel]

Hi
one way:
=IF(ISNA(MATCH(value,$A$1:$A$1000,0)),IF(ISNA(MATCH
(value,$S$1:$S$1000,0)),"no value found",VLOOKUP
($S$1:$T$1000,2,0)),VLOOKUP($A$1:$A$1000,2,0))

 

[Rekoj]

The easiest way would be to test if it is found in the
first column with an If(iserror(. If it produces and error
when you do a vlookup, then have the true portion of the
If statement look in the list in Column S. If it is false,
have the if statment look in column A.

For example:
=if(iserror(vlookup("ACA",ColumnAList,2,0)),vlookup
("ACA",ColumnSList,2,0),vlookup("ACA",ColumnAList,2,0))

Rekoj