Lookup a text value based on multiple conditions

N

noodle

New member
Joined
Jun 12, 2013
Messages
5
Reaction score
0
Points
0
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?

View attachment lookup-test.xlsx

Thanks!
 
You can use a simple VLOOKUP. In cell C2, put this and then copy it down:
=VLOOKUP(B2,$F$16:$G$24,2,FALSE)
 
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.
 
why do you need it to capture your Country list? The list of cities is unique.
 
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),"")
 
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),"")
 
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...
 
The formula I provided looks up based on both city and country.
 
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!
 
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).
 
You must log in or register to reply here.
Back
Top