Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Vlookup on multiple tables

This is a discussion on Vlookup on multiple tables within the Excel Questions forums, part of the Question Forums category; Hello, I'm attemting to perform a VLOOKUP on multiple tables, but am having challenges. First of all I don't think ...

  1. #1
    Board Regular
    Join Date
    Apr 2003
    Posts
    81

    Default Vlookup on multiple tables

    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

  2. #2
    Cam
    Cam is offline
    Board Regular
    Join Date
    May 2002
    Location
    Canada
    Posts
    167

    Default

    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.
    Cam B.

  3. #3
    Board Regular
    Join Date
    Apr 2003
    Posts
    81

    Default

    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

  4. #4
    Cam
    Cam is offline
    Board Regular
    Join Date
    May 2002
    Location
    Canada
    Posts
    167

    Default

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

  5. #5
    Board Regular
    Join Date
    Apr 2003
    Posts
    81

    Default

    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?

  6. #6
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default

    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
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  7. #7
    Board Regular
    Join Date
    Apr 2003
    Posts
    81

    Default

    CAM B,

    YES!!!!!!

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

    Dalex

  8. #8
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default

    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),I F(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

  9. #9
    Board Regular
    Join Date
    Apr 2003
    Posts
    81

    Default

    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

  10. #10
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default

    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

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com