V-Lookup from multiple sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Working with vlookups 4
EXCEL - IF(IFERROR(VLOOKUP question 0
Lookups - how to obtain data from a master spreadsheet into another 5
Multipule vlookups 1
VLookup using lookup table 1
Excel Move or Copy Stopped Working? 0
Excel vba code to match duplicates 4
Filter, sort, lookup from other sheet 2

Back
Top