Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Lookup a text value based on multiple conditions

  1. #1

    Lookup a text value based on multiple conditions



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have created a spreadsheet that uses conditional drop-down menus created with data validation.

    What I now want to do is have my spreadsheet populate with a code, based on what was selected in the drop-down lists. I have the list of all possible codes, but I can't figure out how to make Excel look them up. Maybe I need VBA, I really don't know.

    In case my explanation is not clear, I have attached a file showing what I am trying to do. I want the C column to populate with the correct code from G16:G24. How can I acheive this?

    lookup-test.xlsx

    Thanks!

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    You can use a simple VLOOKUP. In cell C2, put this and then copy it down:
    =VLOOKUP(B2,$F$16:$G$24,2,FALSE)

  3. #3
    Thanks for the reply. However it doesn't quite work with my scenario. I realize that with the dummy spreadsheet I uploaded, the "code" is different for every city, but with what I am trying to accomplish, the code is dependent on 2 or more different items. I.e., in my dummy spreadsheet, lets pretend that my code is dependent on both Country and City.

    I tried adjusting the formula to:
    =VLOOKUP(B2,$E$16:$G$24,2,FALSE)

    So that it would also capture my Country list, but VLOOKUP doesn't like that and give me a #N/A.

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    why do you need it to capture your Country list? The list of cities is unique.

  5. #5
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Ah, you don't want an NA in the case that they have selected a country but have yet to select a city. In that case, use this:
    =IFERROR(VLOOKUP(B2,$F$16:$G$24,2,FALSE),"")

  6. #6
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    191
    Articles
    0
    Excel Version
    2010
    If your real cities list is not unique, try:
    =IFERROR(LOOKUP(2,1/($E$16:$E$24=A2)/($F$16:$F$24=B2),$G$16:$G$24),"")
    This website wants to know your momentum - | Deny | | Allow |

  7. #7
    Hi guys,

    Thanks for your help. I guess the fake spreadsheet I uploaded wasn't a good example. In my real spreadsheet (which I did not want to publicly upload), the codes are unique based on both "city" and "country". (Actually, in the real thing, I want to look up G/L numbers and tax codes, based on different types of travel by different types of employees).

    So lets pretend that we need to look up both country and city to get the code.

    After doing some more reading, I think I have to use some kind of INDEX-MATCH method, so I am working on that...

  8. #8
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    191
    Articles
    0
    Excel Version
    2010
    The formula I provided looks up based on both city and country.
    This website wants to know your momentum - | Deny | | Allow |

  9. #9
    Thanks, Joe! I must have done something wrong the first time I tried to use your formula. It works great. Now, I just have to get it working in my real spreadsheet, which has the lookup values on a separate sheet.... I'm sure I'll be able to figure it out though.

    But I also want to understand how this formula works and have a couple of questions:

    1. What does the 2,1 at the beginning refer to?

    2. The formula looks to my naive eye like it uses division (/), but I realize that mustn't really be the case. What does the / do?

    3. Why do we need to add a $ before each part of the cell reference?

    thanks a bunch!

  10. #10
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    191
    Articles
    0
    Excel Version
    2010
    2 is the lookup value, 1 is the value that is divided (re your point 2) by the two following arrays of TRUE/FALSE values, which are treated as 1/0 respectively and hence you end up with arrays of either 1/1 or 1/0. Fortunately LOOKUP ignores error values so only matches the last 1 value it finds (which hopefully is the only 1).
    This website wants to know your momentum - | Deny | | Allow |

Page 1 of 2 1 2 LastLast

Posting Permissions

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