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

    Vlookup 2 tables (excel 2002/03)

    Hi

    I am trying to do the following I want to look up A6 from a table called FoodPrice, if it can't find it there look in a table called colist I would be grateful for any help.


    =IF(ISNA(VLOOKUP(A6,FoodPrice,4,0),VLOOKUP(A6,Coli st,4,0))

    Thanks

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Vlookup 2 tables (excel 2002/03)

    Try

    =IF(ISNA(VLOOKUP(A6,FoodPrice,4,FALSE)),VLOOKUP(A6 ,Colist,4,FALSE),VLOOKUP(A6,FoodPrice,4,FALSE))

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

    Re: Vlookup 2 tables (excel 2002/03)

    HI Hans

    Thanks for your prompt reply I will apply it and let you know how I get on.

    Thanks again

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

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

    Re: Vlookup 2 tables (excel 2002/03)

    Hi Hans

    Thank you very much your formula worked fine, could I impose on you to show me if a further table could be added say Agriculture for instance. Or is there a limit?


    Many Thanks

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

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup 2 tables (excel 2002/03)

    Edited by HansV to break very long line that caused horizontal scrolling. The formula should be entered as one line, though.

    Try:

    <pre>=IF(ISNA(VLOOKUP(A6,FoodPrice,4,FALSE)),if(is na(VLOOKUP(A6,Colist,4,FALSE)),
    VLOOKUP(A6,Agriculture,4,FALSE),VLOOKUP(A6,Colist, 4,FALSE)),VLOOKUP(A6,FoodPrice,4,FALSE))
    </pre>

    Legare Coleman

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

    Re: Vlookup 2 tables (excel 2002/03)

    HI Legare

    Thanks very much for the reply and thanks to all who responded.

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

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Vlookup 2 tables (excel 2002/03)

    The limit is 7 nested functions AFAIK!
    (Its one better than yours Steve!)
    Regards,
    Rudi

  8. #8
    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: Vlookup 2 tables (excel 2002/03)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I was thinking you could have 7 iFs, but that you are correct, 7 nested...

    Steve

  9. #9
    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: Vlookup 2 tables (excel 2002/03)

    <P ID="edit" class=small>(Edited by sdckapr on 30-Mar-05 10:04. Corrected my mistake (Thanks Rudi for pointing it out))</P>The limit is 7 nested functions.

    Steve

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

    Re: Vlookup 2 tables (excel 2002/03)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    If the retrieval result is a number...

    =LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},VLOOK UP(A6,FoodPrice,4,0),VLOOKUP(A6,Colist,4,0),VLOOKU P(A6,Agriculture,4,0)))

    The idiom is due to fairwinds: http://tinyurl.com/6db2f
    Microsoft MVP - Excel

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

    Re: Vlookup 2 tables (excel 2002/03)

    Hi Aladin

    Thanks for your reply, it looks very interesting. I would like to understand the first part up to choose, I will give it a try and let you know.

    Thanks

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

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

    Re: Vlookup 2 tables (excel 2002/03)

    LOOKUP(9.99999999999999E+307,Reference)

    returns the last numerical value from Reference.

    Reference can be either a range or an array (a vector). F2:F10 is a range, while {#N/A,#N/A,5,2} is an array.

    CHOOSE(Idx,Value1,...,ValueN)

    where N is between 1 and 29 inclusive, allows to pick out a value from the list of values Value1,...ValueN whose position matches the Idx value. For example:

    CHOOSE(2,MAX(A1:B1),MAX(E1:F1)

    picks out the value that MAX(E1:F1) returns.

    CHOOSE({1,2,3},VLOOKUP(...),VLOOKUP(...),VLOOKUP(. ..))

    has an Idx that is a constant array of index numbers: {1,2,3}. This causes to pick out all of the values that 3 VLOOKUP will return. The result will look like, e.g.

    {205.80,#N/A,#N/A}

    or

    {217.65,185.43,#N/A}

    or

    {#N/A,#N/A,#N/A}

    LOOKUP with the big number as lookup value will return 205.80, 185.43, and #N/A, respectively.
    Microsoft MVP - Excel

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

    Re: Vlookup 2 tables (excel 2002/03)

    HI Aladin

    Thanks for explanation.

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

Posting Permissions

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