# Drop Down Menu That Returns a Specific Value

1. ## Drop Down Menu That Returns a Specific Value

I have a data validation table that contains multiple cities. When a city is selected from the drop down menu, I want a value to be placed in a cell next to it.

How can I do this?
Population.png

2. ## Re: Drop Down Menu That Returns a Specific Value

See this...

http://contextures.com/xlFunctions02.html

3. ## Re: Drop Down Menu That Returns a Specific Value

Hi shawner

Have a look here, with reference to data validation with a dependant list.

http://www.myonlinetraininghub.com/e...ependent-lists

4. ## Re: Drop Down Menu That Returns a Specific Value

hi shawner, welcome to the forum. you can use VLOOKUP. assuming "Atlanta" in C4 & the list of Cities & Population in F3:G100, try this in D4:
=VLOOKUP(C4,\$F\$3:\$G\$100,2,0)

this is to find C4 ("Atlanta") in F3:F100 & when it finds it, return the 2nd column next to it. next time, Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

5. ## Re: Drop Down Menu That Returns a Specific Value

=if(A1<>"",VLOOKUP(A1,C2:D10,2,FALSE),"")

I can not tell the ranges from the picture. So adjust A1 to cell with DV. C2:D10 to table of states/population, excluding header.

6. ## Re: Drop Down Menu That Returns a Specific Value

Thank you so much, everyone. You guys are freakin' awesome!

However, I am unsuccessful in my task. Here's what I've tried:

I input the formula =VLOOKUP(B2,Pricing!A2:B27,2,FALSE) and I receive the error: "A user has restricted values that can be entered into this cell." What am I doing wrong?

Pricing Tool.xlsxPopulation Tab.jpgPricing Tab.jpg

7. ## Re: Drop Down Menu That Returns a Specific Value

shawner

Can you not get shot of the data validation in C2 and use:
=INDEX(Population!B2:B27,MATCH(B2,Population!A2:A27,0))

8. ## Re: Drop Down Menu That Returns a Specific Value

Thank you, Kevin - I copy-and-pasted your formula and it works. I will take time to learn more about 'INDEX' and 'MATCH' functions.

Regards,
Shawner.

9. ## Re: Drop Down Menu That Returns a Specific Value

No problem shawne, glad to of helped. See the link below for more on the index & match.

INDEX & MATCH

10. ## Re: Drop Down Menu That Returns a Specific Value

Clear the data validation rule for cell C2.

Select cell C2
Goto the Data tab
Data Validation
Click the Clear All button
OK out

Then, enter this formula in C2:

=VLOOKUP(B2,Population!A2:B27,2,0)

There are currently 1 users browsing this thread. (0 members and 1 guests)