# Vlookup with multiple sources

#### malehot888

##### Board Regular
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?

### 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?

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

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)

Hi,

If the return value is text,

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

Replies
0
Views
404
Replies
0
Views
313
Replies
2
Views
949
Replies
13
Views
820
Replies
27
Views
654

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.

### Which adblocker are you using?

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

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