Vlookup with multiple sources

malehot888

Board Regular
Joined
Sep 16, 2005
Messages
52
Hello,

I was hoping somebody can help me out with my problem! I have a worksheet that i need to vlookup data from a seperate workbook. This workbook contains 4 worksheets, each containing 60000 lines of data, and that data lies somewhere in one of those four worksheets.

I've tried nesting 3 vlookups with if/isna statements, but it ends up way too long it to work, plus it's not very efficient.

I'm aware of using vba code to create a function using if else,else,else, but is there any way of doing it w/o resorting to using vb?

Thanks in advance!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
malehot888 said:
Hello,

I was hoping somebody can help me out with my problem! I have a worksheet that i need to vlookup data from a seperate workbook. This workbook contains 4 worksheets, each containing 60000 lines of data, and that data lies somewhere in one of those four worksheets.

I've tried nesting 3 vlookups with if/isna statements, but it ends up way too long it to work, plus it's not very efficient.

I'm aware of using vba code to create a function using if else,else,else, but is there any way of doing it w/o resorting to using vb?

Thanks in advance!

This may seem troublesome, but I think it's pretty efficient.

In A1:A4 enter a regular Vlookup formula.

In B1 enter =IF(ISNA(A1),"",A1) and copy down to B4

Where you want the answer to appear enter

=INDEX(B1:B4,MATCH(9.99999999999999E+307,B1:B4))
 
Upvote 0
Hi malehot888:

Formulation proposed by Brian will work fine if the looked up entity is numeric.

For a more generalized case, let us look at the following approach ...
Book3
BCDE
1jkl
2
3#N/A
4jkl
5#N/A
6#N/A
7
Sheet5


cells C3:C7 are the results of the VLOOKUP in Sheet 1 through 4. For a bit of automation, I used the following formula in cell C3 ...

=VLOOKUP(LookUpEntry,INDIRECT("Sheet"&ROWS($A$1:$C1)&"!"&LookUpRange),2,0)

and then copied it down to C3 through C5

Formula in cell C1 is ...
=INDEX(C3:C6,MATCH(TRUE,INDEX(NOT(ISNA(C3:C6)),0)),0)
 
Upvote 0
Hi,

If the return value is text,

=LOOKUP(REPT("z",255),CHOOSE({0,1,2,3,4,5},"Not Found",VLOOKUP(A5,[Book1.xls]Sheet1!$A$4:$B$100,2,0),VLOOKUP(A5,[Book1.xls]Sheet2!$A$4:$B$100,2,0),VLOOKUP(A5,[Book1.xls]Sheet3!$A$4:$B$100,2,0),VLOOKUP(A5,[Book1.xls]Sheet4!$A$4:$B$100,2,0)))

If it's numeric then,

=LOOKUP(9.99999999e+307,CHOOSE({0,1,2,3,4,5},0,VLOOKUP(A5,[Book1.xls]Sheet1!$A$4:$B$100,2,0),VLOOKUP(A5,[Book1.xls]Sheet2!$A$4:$B$100,2,0),VLOOKUP(A5,[Book1.xls]Sheet3!$A$4:$B$100,2,0),VLOOKUP(A5,[Book1.xls]Sheet4!$A$4:$B$100,2,0)))

HTH
 
Upvote 0

Forum statistics

Threads
1,217,347
Messages
6,136,046
Members
449,983
Latest member
mschaef6

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top