V-Lookup from multiple sheets

[Byron720]

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.

 

[bj]

tr
=if(iserror(vlookup(PN,'Sheet1'!range1,2,0)),ifiserror(vlookup(PN,'Sheet2'!range2,2,0),vlookup(PN,'Sheet3'!range3,2,0),vlookup(PN,'Sheet2'!range2,2,0)),vlookup(PN,'Sheet1'!range1,2,0))

 

[Domenic]

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$100,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(TRUE,COUNTIF(INDIRECT("'"&
$A$2:$A$4&"'!D2100"),B2)>0,0))&"'!D2:E100"),2,0)

Hope this helps!

 

[Byron720]

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

 

[Peo Sjoblom]

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

 

[Byron720]

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

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$100,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(TRUE,COUNTIF(INDIRECT("'"&
$A$2:$A$4&"'!D2100"),B2)>0,0))&"'!D2:E100"),2,0)

Hope this helps!

 

[T. Valko]

Here's another one:

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

Biff

 

[T. Valko]

That can be further reduced to:

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

Biff