V-Lookup from multiple sheets
 

How do I use the vlookup function (or else) to find data from 3 different
sheets? For example I need to extract information about a part # but the list
is 3 sheets long. The part #'s are numeric and alphanumeric.

V-Lookup from multiple sheets
 

try
=if(iserror(vlookup(PN,'Sheet1'!range1,2,0)),ifise rror(vlookup(PN,'Sheet2'!range2,2,0),vlookup(PN,'S heet3'!range3,2,0),vlookup(PN,'Sheet2'!range2,2,0) ),vlookup(PN,'Sheet1'!range1,2,0))

"Byron720" wrote:

How do I use the vlookup function (or else) to find data from 3 different
sheets? For example I need to extract information about a part # but the list
is 3 sheets long. The part #'s are numeric and alphanumeric.

V-Lookup from multiple sheets
 

Assumptions:

1) Sheet1, Sheet2, and Sheet3 contain the lookup tables

2) On each sheet, D2:E100 contains the lookup table

3) On the result sheet, B2 contains the lookup value

Formula:

On the result sheet...

=IF(ISNA(VLOOKUP(B2,'Sheet1'!$D$2:$E$100,2,0)),IF( ISNA(VLOOKUP(B2,'Sheet2
'!$D$2:$E$100,2,0)),VLOOKUP(B2,'Sheet3'!$D$2:$E$10 0,2,0),VLOOKUP(B2,'Shee
t2'!$D$2:$E$100,2,0)),VLOOKUP(B2,'Sheet1'!$D$2:$E$ 100,2,0))

Alternatively, let A2:A4 contain Sheet1, Sheet2, and Sheet3, then try
the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=VLOOKUP(B2,INDIRECT("'"&INDEX($A$2:$A$4,MATCH(TRU E,COUNTIF(INDIRECT("'"&
$A$2:$A$4&"'!D2:D100"),B2)0,0))&"'!D2:E100"),2,0)

Hope this helps!

In article ,
Byron720 wrote:

How do I use the vlookup function (or else) to find data from 3 different
sheets? For example I need to extract information about a part # but the list
is 3 sheets long. The part #'s are numeric and alphanumeric.

V-Lookup from multiple sheets
 

I'm almost there, but the formula only works with data on the first sheet.
For part #'s on sheets 2 and 3 it gives me #NAME? error. I changed the PN for
the cell, Sheets 1, 2, and 3 for the right names and fixed the ranges to
$1:$65536. Can you take a look at the formula and find the error? b'cause I
don't see it

Byron

"bj" wrote:

try
=if(iserror(vlookup(PN,'Sheet1'!range1,2,0)),ifise rror(vlookup(PN,'Sheet2'!range2,2,0),vlookup(PN,'S heet3'!range3,2,0),vlookup(PN,'Sheet2'!range2,2,0) ),vlookup(PN,'Sheet1'!range1,2,0))

"Byron720" wrote:

How do I use the vlookup function (or else) to find data from 3 different
sheets? For example I need to extract information about a part # but the list
is 3 sheets long. The part #'s are numeric and alphanumeric.

V-Lookup from multiple sheets
 

Name error means you have a typo so you need to check that all sheet names
etc are correct and that there are no spelling errors
bj missed a parenthesis, it should be if(iserror not ifiserror although you
might want to change that to IF(ISNA instead since that is the error you are
looking to avoid, any other error might be good to know about


--
Regards,

Peo Sjoblom



"Byron720" wrote in message
...
I'm almost there, but the formula only works with data on the first sheet.
For part #'s on sheets 2 and 3 it gives me #NAME? error. I changed the PN
for
the cell, Sheets 1, 2, and 3 for the right names and fixed the ranges to
$1:$65536. Can you take a look at the formula and find the error? b'cause
I
don't see it

Byron

"bj" wrote:

try
=if(iserror(vlookup(PN,'Sheet1'!range1,2,0)),ifise rror(vlookup(PN,'Sheet2'!range2,2,0),vlookup(PN,'S heet3'!range3,2,0),vlookup(PN,'Sheet2'!range2,2,0) ),vlookup(PN,'Sheet1'!range1,2,0))

"Byron720" wrote:

How do I use the vlookup function (or else) to find data from 3
different
sheets? For example I need to extract information about a part # but
the list
is 3 sheets long. The part #'s are numeric and alphanumeric.

V-Lookup from multiple sheets
 

You guys made my day !!!!!!!!!!!!

"Domenic" wrote:

Assumptions:

1) Sheet1, Sheet2, and Sheet3 contain the lookup tables

2) On each sheet, D2:E100 contains the lookup table

3) On the result sheet, B2 contains the lookup value

Formula:

On the result sheet...

=IF(ISNA(VLOOKUP(B2,'Sheet1'!$D$2:$E$100,2,0)),IF( ISNA(VLOOKUP(B2,'Sheet2
'!$D$2:$E$100,2,0)),VLOOKUP(B2,'Sheet3'!$D$2:$E$10 0,2,0),VLOOKUP(B2,'Shee
t2'!$D$2:$E$100,2,0)),VLOOKUP(B2,'Sheet1'!$D$2:$E$ 100,2,0))

Alternatively, let A2:A4 contain Sheet1, Sheet2, and Sheet3, then try
the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=VLOOKUP(B2,INDIRECT("'"&INDEX($A$2:$A$4,MATCH(TRU E,COUNTIF(INDIRECT("'"&
$A$2:$A$4&"'!D2:D100"),B2)0,0))&"'!D2:E100"),2,0)

Hope this helps!

In article ,
Byron720 wrote:

How do I use the vlookup function (or else) to find data from 3 different
sheets? For example I need to extract information about a part # but the list
is 3 sheets long. The part #'s are numeric and alphanumeric.

V-Lookup from multiple sheets
 

Here's another one:

=VLOOKUP(A1,IF(COUNTIF(Sheet1!A:A,A1),Sheet1!A:B,I F(COUNTIF(Sheet2!A:A,A1),Sheet2!A:B,IF(COUNTIF(She et3!A:A,A1),Sheet3!A:B))),2,0)

Biff

"Byron720" wrote in message
...
How do I use the vlookup function (or else) to find data from 3 different
sheets? For example I need to extract information about a part # but the
list
is 3 sheets long. The part #'s are numeric and alphanumeric.

V-Lookup from multiple sheets
 

That can be further reduced to:

=VLOOKUP(A1,IF(COUNTIF(Sheet1!A:A,A1),Sheet1!A:B,I F(COUNTIF(Sheet2!A:A,A1),Sheet2!A:B,Sheet3!A:B)),2 ,0)

Biff

"T. Valko" wrote in message
...
Here's another one:

=VLOOKUP(A1,IF(COUNTIF(Sheet1!A:A,A1),Sheet1!A:B,I F(COUNTIF(Sheet2!A:A,A1),Sheet2!A:B,IF(COUNTIF(She et3!A:A,A1),Sheet3!A:B))),2,0)

Biff

"Byron720" wrote in message
...
How do I use the vlookup function (or else) to find data from 3 different
sheets? For example I need to extract information about a part # but the
list
is 3 sheets long. The part #'s are numeric and alphanumeric.