Lookup value in one list to return corresponding value from another

**excelshredder** · 06-26-2013, 10:30 AM

Untitled.jpg
I have been stuck in this problem for many days now and would really appreciate any help on this.

Problem: I have two lists of data. Products and their corresponding prices.
What I want to do is to create a data validation drop-down list of products. When a product is selected from this drop-down list, I want the corresponding price to be displayed in an adjacent cell.

I thought I could use a nested IF condition but it has limitations in terms of number of arguments.
Please help.

Thanks in advance.

**AlKey** · 06-26-2013, 10:39 AM

If the "Products" list has unique values you could use VLOOKUP function. It will lookup value selected from the validation list.

**etaf** · 06-26-2013, 10:48 AM

an index match would work better

Use data validation > list and use the range for the Products - In my example in E2
and then in the next cell use index match
=INDEX(B:B,MATCH(E2,A:A,0))

see the attached example

**excelshredder** · 06-26-2013, 02:56 PM

Thanks etaf. It worked. Thanks a ton.

**excelshredder** · 06-26-2013, 02:57 PM

@Alkey: unfortunately it isnt a particular value but infact a range of values that the user will select from. Thanks

**etaf** · 06-26-2013, 02:59 PM

your welcome , I think Alkey, had the same suggestion as i have - except using vlookup instead of index/match

if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.