How to link form control dropdown box to formula cell

**warriorpoet7176** · 03-09-2014, 05:43 AM

Hi To All

Warm Greetings !!

I have an excel that has dropdown column from form control, in F4 there is a formula of vlookup that take the range from A2 to B20 in consideration but the reference cell that is given to vlookup formula to pick corresponding value is E4 which is also the reference cell of dropdown box and therefore its not showing any name rather than a value, problem with the formula is that when we select any name from dropdown it does not respond to any value in F4 despite changing values in E4.

I am using these setting for the first time so maybe I am making some mistake kindly help me in this & also mention the mistake I was making in it.

For the convenience of readers' image & excel file is attached

PFA
drop down.png

Thanks In advance !!

Best Regards

**hemesh** · 03-09-2014, 06:10 AM

you could use formula like
=VLOOKUP(INDEX($A$2:$A$20,$E4),$A$2:$B$20,2,0)

you were getting wrong results because this control always shows the number of item and vlookup is looking for that number and it is not finding any match, so by passing that item number to index you will get the name of that item
=INDEX($A$2:$A$20,$E4)

now use this name or in other words you can nest this index for vlookup to get the results
=VLOOKUP(INDEX($A$2:$A$20,$E4),$A$2:$B$20,2,0)

**popipipo** · 03-09-2014, 07:26 AM

If you use this formula to find name:
=INDEX($A$2:$A$20,$E4)

Then use this formula to find the code:
=INDEX($A$2:$B$20,$E4,2)

**warriorpoet7176** · 03-09-2014, 08:53 AM

Hi Hemesh,

From last night I was trying to get this problem solved through microsoft help section & internet as well but due to insufficient information & Syntax over there it was all of no avail, I remembered once before also you resolved my problem like this, once again Thank You Sooooooo Muchhhhhh, your idea worked for me. I am posting to you one link please see if you have any idea to solve this problem I actually tried to make two posts for this problem but still till date there are 116 view with one incomplete reply, if you have any superficial idea to how to go around this problem plz help, PSB.
http://www.excelforum.com/excel-prog...ml#post3606139

Originally Posted by hemesh

you could use formula like
=VLOOKUP(INDEX($A$2:$A$20,$E4),$A$2:$B$20,2,0)

you were getting wrong results because this control always shows the number of item and vlookup is looking for that number and it is not finding any match, so by passing that item number to index you will get the name of that item
=INDEX($A$2:$A$20,$E4)

now use this name or in other words you can nest this index for vlookup to get the results
=VLOOKUP(INDEX($A$2:$A$20,$E4),$A$2:$B$20,2,0)

**warriorpoet7176** · 03-09-2014, 09:08 AM

Originally Posted by popipipo

If you use this formula to find name:
=INDEX($A$2:$A$20,$E4)

Then use this formula to find the code:
=INDEX($A$2:$B$20,$E4,2)

Hi,
Thanks for your immediate reply Thank You Soo Much, your idea worked in both ways.
Kudos

Sir I want to have your attention for one of my unsolved problem on excelforum, plz see if you have any information about it Or any superficial idea to solve it as well

PSB

http://www.excelforum.com/excel-prog...ml#post3606139

Regards