Vlookup on mutiple worksheets

[nickd via OfficeKB.com]

Hello All,

I have a worksheet (sheet1) where I would like to do a vlookup in col b1,
from data that is in worksheet 2 (sheet2, a1:d100), and worksheet 3 ( sheet 3,
a1:d100). the output for the vlookup in sheet 1 is in either sheet 2 or sheet
3.

I am new to excel and can do a vlookup by referncing 1 sheet, but having
massive problems with referencing 2 sheets.

Any help would be very appreciated, and an example spreadsheet with formula
would be great

Thanks

 

[Jim Thomlinson]

You can use ISNA to determine if a Vlookup is going to return something. So
you could do something like this...

=if(isna(vlookup(A1, sheet2!a1:d100, 2, false)), vlookup(A1, sheet3!a1:d100,
2, false), vlookup(A1, sheet2!a1:d100, 2, false))

 

[T. Valko]

the output ... is in either sheet 2 or sheet 3.

One way:

=VLOOKUP(A1,IF(COUNTIF(Sheet2!A1:A100,A1),Sheet2!A1100,Sheet3!A1100),2,0)

 

[nickd via OfficeKB.com]

Thanks Jim,

This is not working for me ???

You can use ISNA to determine if a Vlookup is going to return something. So
you could do something like this...

=if(isna(vlookup(A1, sheet2!a1:d100, 2, false)), vlookup(A1, sheet3!a1:d100,
2, false), vlookup(A1, sheet2!a1:d100, 2, false))

Hello All,

[quoted text clipped - 10 lines]

Thanks

 

[nickd via OfficeKB.com]

Thanks Jim,

This is not working for me ???

You can use ISNA to determine if a Vlookup is going to return something. So
you could do something like this...

=if(isna(vlookup(A1, sheet2!a1:d100, 2, false)), vlookup(A1, sheet3!a1:d100,
2, false), vlookup(A1, sheet2!a1:d100, 2, false))

Hello All,

[quoted text clipped - 10 lines]

Thanks

 

[nickd via OfficeKB.com]

Thanks Jim,

This is not working for me ???

You can use ISNA to determine if a Vlookup is going to return something. So
you could do something like this...

=if(isna(vlookup(A1, sheet2!a1:d100, 2, false)), vlookup(A1, sheet3!a1:d100,
2, false), vlookup(A1, sheet2!a1:d100, 2, false))

Hello All,

[quoted text clipped - 10 lines]

Thanks

 

[nickd via OfficeKB.com]

This only gives me the output from sheet 2, those in sheet 3 have returned
n/a.

the output ... is in either sheet 2 or sheet 3.

One way:

=VLOOKUP(A1,IF(COUNTIF(Sheet2!A1:A100,A1),Sheet2!A1100,Sheet3!A1100),2,0)

Hello All,

[quoted text clipped - 13 lines]

Thanks

 

[T. Valko]

Here's a small sample file that demonstrates this:

http://cjoint.com/?cuw0bYYNYZ

I'm assuming:

the output ... is in either sheet 2 or sheet 3.

Which I interpret to mean, the lookup_value *does* exist. It's on one sheet
or the other.



--
Biff
Microsoft Excel MVP

This only gives me the output from sheet 2, those in sheet 3 have returned
n/a.

the output ... is in either sheet 2 or sheet 3.

One way:

=VLOOKUP(A1,IF(COUNTIF(Sheet2!A1:A100,A1),Sheet2!A1100,Sheet3!A1100),2,0)

Hello All,

[quoted text clipped - 13 lines]

Thanks

 

[nickd via OfficeKB.com]

Thank you, this has benn very helpful, much appreciated !

Here's a small sample file that demonstrates this:

http://cjoint.com/?cuw0bYYNYZ

I'm assuming:

the output ... is in either sheet 2 or sheet 3.

Which I interpret to mean, the lookup_value *does* exist. It's on one sheet
or the other.

This only gives me the output from sheet 2, those in sheet 3 have returned
n/a.

[quoted text clipped - 10 lines]

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thank you, this has benn very helpful, much appreciated !

Here's a small sample file that demonstrates this:

http://cjoint.com/?cuw0bYYNYZ

I'm assuming:

the output ... is in either sheet 2 or sheet 3.

Which I interpret to mean, the lookup_value *does* exist. It's on one
sheet
or the other.

This only gives me the output from sheet 2, those in sheet 3 have
returned
n/a.

[quoted text clipped - 10 lines]

Thanks