Vlookup on multiple tables

Dalex

Board Regular
Joined
Apr 25, 2003
Messages
81
Hello,

I'm attemting to perform a VLOOKUP on multiple tables, but am having challenges. First of all I don't think it is possible to perform a VLOOKUP on multiple tables so I thought it practical to copy the tables's data into one large table, then perform the VLOOKUP from that that one "master table". Unfortunately this does not work UNLESS I put the "master table" in some kind of ascending order. I prefer not to use macros...only formulas.

Here is the code I am currently working with:

=IF((C67>0),VLOOKUP(C67,Entry!$AI$4:AI59,2),"")

Any suggestion will be much appreciated.

Thanks.

Dalex
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
What about using an Index Match combination like the following

=IF(a1=0,INDEX(D2:F8,MATCH(a1,D2:D8,0),2),"")

Edit cell references as required.
 
Upvote 0
Cam B.

Thank you very much for your reply, however I keep getting that dreaded "#N/A" error message. Here is the formula I entered based on your reccomendation:

=IF(C60>0,INDEX(C4:D50,MATCH(C60,C4:D50,2),2,""))



Dalex
 
Upvote 0
Yes. My ultimate goal is to be able to enter a project number in cell C60 (example:5598), and have the project name automatically populate in the cell next to it. Again, this will depend on what project number is typed.

Does this make sense?
 
Upvote 0
Are you looking for an exact or an approximate match? If you are looking for an exact match only, then your table does not need to be sorted. Simply change this formula --

=IF((C67>0),VLOOKUP(C67,Entry!$AI$4:AI59,2),"")

to this one --

=IF((C67>0),VLOOKUP(C67,Entry!$AI$4:AI59,1,FALSE),"")

BTW, was the original formula above working at all? You have given it a 1 column range [ AI4:AI59 ] but are referencing, wwith the ",2" part, a second column. That should have generated an error. In the revised formula above, I have changed the offset of 2 to an offset of 1; if required as 2, then increase the target array from AI4:AI59 to at least AI4:AJ59
 
Upvote 0
CAM B,

YES!!!!!!

You are greatness!! Thanks a million and enjoy your weekend!

Dalex
 
Upvote 0
You can perform a lookup on multiple tables:

Say you have your data tables in A1:B10 in Sheet 1, Sheet 2 and Sheet 3 respectively.

E2 on Sheet 1 contains a value from column A that you wish to lookup from column B.

In F2 (Sheet 1):
=IF(COUNTIF(Sheet1!A1:A10,E2),VLOOKUP(E2,Sheet1!A1:B10,2,0),IF(COUNTIF(Sheet2!A1:A10,E2),VLOOKUP(E2,Sheet2!A1:B10,2,0),IF(COUNTIF(Sheet3!A1:A10,E2),VLOOKUP(E2,Sheet3!A1:B10,2,0),"not found in any Table")))

The formula looks firstly at Sheet 1 A1:B10, and if the value is found returns the result to E2. If not found, the formula looks at Sheet 2 A1:B10, then to Sheet 3 etc.

HTH

Mike
 
Upvote 0
Mike,

I appreciate your help, however I have the three all within sheet1. I'm playing with your formula but am not having luck.

Dalex
 
Upvote 0
Dalex,

If you are still stuck, send me a Private Message (click my Profile at the bottom of this post), and include your email address. I will send you an Excel workbook. All Tables have been moved to the one sheet, and the formula has been adjusted accordingly. The formula in the workbook uses named ranges that makes the formula more readable.

I have also added some conditional formatting that highlights the row and Table that contains the result.

Regards,

Mike
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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