1. Registered User
Join Date
Apr 2012
Posts
4

Hello,
I have a drop down menu and would like another cell to automatically insert a price for each item in my drop down menu. Would love anyone who knows to help me.

Thanks very much. Using Excel for mac

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi,

You can keep a table on another sheet which has the price for each item. Then, you can have a VLOOKUP formula which returns the price depending on which item was picked from your data validation dropdown list.

3. Registered User
Join Date
Apr 2012
Posts
4

## Drop down menu & formula

Thank you very much Colin,
I'm not familiar with that type of formula, any chance you could create so I can see what it looks like?
Greatly appreciated.

4. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Sure..

So, I've attached an example. On Sheet1 there's a dropdown list in C2. Pick an item and the corresponding price is returned in cell C3. The formula in C3 is this:

=IF(C2<>"",VLOOKUP(C2,Table1,2,0),"")

So, this formula says, "If C2 is blank then return a blank, otherwise look up the exact C2 value in the first column of Table1 and return the corresponding value from the second column of Table1".

Table1 is on Sheet2.

The reason I used a table is because you can add more items to it and the data validation dropdown list in C2 will automatically expand with it. Tables were introduced into Excel in version 2007 so you might not be able to use them (depending on which Mac version you have). If you have an earlier version then let me know and I'll give you a different example.

Bill Jelen has just coordinated a "VLOOKUP week" and collated together lots of articles and videos about VLOOKUP. If you're interested in learning about it then check out the VLOOKUP week blog. You might find Mike Girvin's videos easy to follow (start with video #1).

5. Registered User
Join Date
Apr 2012
Posts
4

## Drop down menu & formula

Thank you so much for doing this Colin, I'm afraid that my version of Excel for mac will not let me open it. It defaults to the Numbers application. Even rich clicking, will not let me choose Excel. Are you able to save it in another format? The file extension I get is XLS

Thanks gain for your kind help

6. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Sure, I've converted it to an .xls.

Code:
`=IF(C2<>"",VLOOKUP(C2,Sheet2!\$B\$3:\$C\$12,2,0),"")`

7. Registered User
Join Date
Apr 2012
Posts
4

## Drop down menu & formula

Hello Colin,
Thank you so much, it's greatly appreciated. I will try this on my sheet.

#### Posting Permissions

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