Vlookup referencing more than one worksheet

[Kris W]

Hi

(Excel 2003)

I have a vlookup formula in worksheet A, which looks up worksheet B, but I
need it to look up worksheet C also - how do I do this?

I also need it to say "Not Listed" instead of "#N/A" as it does at the
moment...

Thanks!

 

[T. Valko]

One way:

=IF(COUNTIF(B!A1:A10,A1)+COUNTIF('C'!A1:A10,A1),VLOOKUP(A1,IF(COUNTIF(B!A1:A10,A1),B!A1:B10,'C'!A1:B10),2,0),"Not
Listed")

 

[Teethless mama]

Download and install the free add-in Morefunc.xll from:
http://www.download.com/Morefunc/3000-2077_4-10423159.html

....then use this formula

=IF(ISNA(VLOOKUP(A1,THREED('B:C'!A1:B10),2,0)),"Not
Listed",VLOOKUP(A1,THREED('B:C'!A1:B10),2,0))