A predefined value is picked, how to show its associated value in the next Cell

**L_Excel** · 08-21-2009, 11:22 AM

I have two columns, “Item”, and “Price”. I have several predefined items and the price for the item respectively. Whenever I pick an item from the list for one cell, I want its price shown in the next cell.

If an item (for example “pencil”) is selected from the drop down list for the cell A2, I want the price of the pencil (for example “2”) automatically shown in the cell B2.

Can someone please show me how to do it? I tried the IF but I have a lot of predefined items, it is almost impossible to do it.

Thanks in advance.

**DonkeyOte** · 08-21-2009, 01:28 PM

I would suggest create a listing which has:

a) the pre-defined "items" in one column
b) the associated prices in the adjacent column

then in the cell adjacent to that containing the "selection" use a VLOOKUP to retrieve the price based on the above table, eg assuming column C holds itsems list and D the prices, selection is made in A1 then:

B1: =VLOOKUP(A1,C:D,2,FALSE)

... see XL Help for more info on VLOOKUP