Returning a Value From A Drop Down List

[Darth_Williams]

Good morning all,

I have a dependant drop down list working at the moment where the first list shows suppliers and the second shows the items that supplier has. I am trying to work it so that once an item has been selected the value will automatically populate in another column. I thought a simple VLOOKUP would solve this but I keep getting #REF ! return.

I have a theory that an Index Match might work but I have no idea how to work one of those.

Does anyone have any ideas?

Thanks

 

[antoanand1981]

Could you please share us some example? or may be you can share test file.

 

[Darth_Williams]

I now have a working, but messy, formula which is:

=IF($C5="Shires",VLOOKUP($D5,Shires2,4,FALSE),IF($C5="Tarmac",VLOOKUP($D5,Tarmac2,4,FALSE),IF($C5="Keyline",VLOOKUP($D5,Keline2,4,FALSE),IF($C5="JamesBurrell",VLOOKUP($D5,JamesBurrell2,4,FALSE),""))))

The suppliers are, Shires, Tarmac, Keyline & JamesBurrell.

I have created tables for each which shows the item description, unit of measurement, value etc... which the above formula seems to like. I'm guessing that if it works I should leave it be

 

[Fluff]

Is this right "Keline2" or should it be "Keyline2"?

 

[Darth_Williams]

it should be Keyline2 but I had a typo when I was naming the table and just decided to live with it as it's only me out of the people who will be using this sheet that understands that formula

 

[Fluff]

If you rename that table you could use
=IFERROR(VLOOKUP($D5,INDIRECT($C5&"2"),4,FALSE),"")

Although it's a volatile formula.