Vlookup on multiple tables

C

Cam

Board Regular

Joined

May 29, 2002

Messages

167

• Apr 25, 2003

• #2

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

D

Dalex

Board Regular

Joined

Apr 25, 2003

Messages

81

• Apr 25, 2003

• #3

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

C

Cam

Board Regular

Joined

May 29, 2002

Messages

167

• Apr 25, 2003

• #4

Will cell C60 show up in the same column or row in ur Master table?

 

Upvote 0

D

Dalex

Board Regular

Joined

Apr 25, 2003

Messages

81

• Apr 25, 2003

• #5

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

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Apr 25, 2003

• #6

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

D

Dalex

Board Regular

Joined

Apr 25, 2003

Messages

81

• Apr 25, 2003

• #7

CAM B,

YES!!!!!!

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

Dalex

 

Upvote 0

E

Ekim

Well-known Member

Joined

Jun 30, 2002

Messages

1,416

• Apr 25, 2003

• #8

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

D

Dalex

Board Regular

Joined

Apr 25, 2003

Messages

81

• Apr 25, 2003

• #9

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

E

Ekim

Well-known Member

Joined

Jun 30, 2002

Messages

1,416

• Apr 25, 2003

• #10

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