Adding validation in excel according to another column from another sheet

[javadeb]

I have a excel sheet called data with 3 columns: id, name
I have another sheet called list with 3 columns: id, name
I have made a data validation on column name in data sheet with this

> formula: =list!A$2:$A$37000

its working fine and gives me a drop down list from my names in list and another formula for column id in my data sheet

> formula: IFERROR(VLOOKUP(B${i},list!A1:B10000,2, FALSE),"")

problem is that if some rows in list sheet have same names, if I change value in drop down list in data sheet, id wont change. What should I change to make it work?

[example file][1]


[1]: Deleted

 

[Fluff]

You will need dependant data validation Create a dynamic dependent drop down list in Excel an easy way - Ablebits.com