Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Multiple Vlookup (Excel 2002)

    Hi

    Is it possible to do a multiiple vlookup for example.

    =VLOOKUP(A2,TABLE1,3,FALSE) (IF NOT FOUND THEN) =VLOOKUP(A2,TABLE2,3,FALSE).

    I can't combine the tables because the item code in A2 may appear in both tables, so the first lookup must be in table 1, but if it can' t be found in table1 then I will accept it from table 2.

    Thanks in advance

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multiple Vlookup (Excel 2002)

    How about this:
    =IF(ISERROR(VLOOKUP(A2,Table1,3,FALSE)),VLOOKUP(A2 ,Table2,3,FALSE),VLOOKUP(A2,Table1,3,FALSE))

    It looks in table1, if not found it generates an error, so it looks in Table2,
    If no error is generated by looking in Table1, it gets the value.

    If it is not found in either, it generates the #N/A error

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Multiple Vlookup (Excel 2002)

    Hi Steve

    Thanks very much , I am most grateful.

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Vlookup (Excel 2002)

    A 2-cell approach...

    X2 (or any other ceonvenient cell)...

    =IF(ISNA(Y2),VLOOKUP(A2,TABLE2,3,0),X2)

    Y2:

    =VLOOKUP(A2,TABLE1,3,0)

    0 stands for FALSE, which lookup functions like VLOOKUP, HLOOKUP, and MATCH also accept as the match-type (range_lookup in MS parlance) specification.

    Note that X2 is the result cell.
    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,948
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Multiple Vlookup (Excel 2002)

    Hi Aladin

    This is an interesting approach , I never realised 0 = false, saves some typying does this mean 1= true?.


    Thanks for your reply

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Vlookup (Excel 2002)

    Yes.
    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multiple Vlookup (Excel 2002)

    More strictly speaking, the way excel works, 0 is false, all other numbers will act as true if used in a formula.

    If you have something that results in TRUE and you convert it to a number (eg by multiplying it by 1), it results in a 1. But any number (other than 0) is considered "True".

    Note in VB (as opposed to excel) a True has a value of -1 not +1.

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Vlookup (Excel 2002)

    <hr>Note in VB (as opposed to excel) a True has a value of -1 not +1.<hr>Handy that, isn't it! <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15>

    Alan

Posting Permissions

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