Vlookup with multiple sources

[malehot888]

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!

 

[Brian from Maui]

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]

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
	B	C	D	E
1		jkl
2
3		#N/A
4		jkl
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]

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