Vlookup on mutiple worksheets
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200802/1

Vlookup on mutiple worksheets
 

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))

--
HTH...

Jim Thomlinson


"nickd via OfficeKB.com" wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200802/1

Vlookup on mutiple worksheets
 

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

One way:

=VLOOKUP(A1,IF(COUNTIF(Sheet2!A1:A100,A1),Sheet2!A 1:D100,Sheet3!A1:D100),2,0)

--
Biff
Microsoft Excel MVP


"nickd via OfficeKB.com" <u35935@uwe wrote in message
news:800544781c781@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200802/1

Vlookup on mutiple worksheets
 

Thanks Jim,

This is not working for me ???

Jim Thomlinson wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200802/1

Vlookup on mutiple worksheets
 

Thanks Jim,

This is not working for me ???

Jim Thomlinson wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200802/1

Vlookup on mutiple worksheets
 

Thanks Jim,

This is not working for me ???

Jim Thomlinson wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200802/1

Vlookup on mutiple worksheets
 

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

T. Valko wrote:
the output ... is in either sheet 2 or sheet 3.

One way:

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

Hello All,

[quoted text clipped - 13 lines]

Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200802/1

Vlookup on mutiple worksheets
 

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


"nickd via OfficeKB.com" <u35935@uwe wrote in message
news:8007640e8e03f@uwe...
This only gives me the output from sheet 2, those in sheet 3 have returned
n/a.

T. Valko wrote:
the output ... is in either sheet 2 or sheet 3.

One way:

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

Hello All,

[quoted text clipped - 13 lines]

Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200802/1

Vlookup on mutiple worksheets
 

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

T. Valko wrote:
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

--
Message posted via http://www.officekb.com

Vlookup on mutiple worksheets
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"nickd via OfficeKB.com" <u35935@uwe wrote in message
news:8008818a5d151@uwe...
Thank you, this has benn very helpful, much appreciated !

T. Valko wrote:
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

--
Message posted via http://www.officekb.com