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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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))
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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)
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,120
Messages
5,857,492
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

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
Top