V-Lookup from multiple sheets

B

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.
 
B

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

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&"'!D2:D100"),B2)>0,0))&"'!D2:E100"),2,0)

Hope this helps!
 
B

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
 
P

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
 
B

Byron720

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

Domenic said:
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&"'!D2:D100"),B2)>0,0))&"'!D2:E100"),2,0)

Hope this helps!
Click to expand...
 
T

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

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
 
Top