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!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Brian from Maui

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

Watch MrExcel Video

Forum statistics

Threads
1,118,057
Messages
5,569,954
Members
412,300
Latest member
Chaneycr
Top