Lookups in Excel - How?

[Feek]

I'm doing some stuff in Excel which I think needs a lookup table of some sort.

Basically I have a number of items and an associated number. I need to select the item using a drop-down and then have the associated number appear in a cell next to the item.

How do I do this? I'm sure it's something simple.

Ta.

[kaiowas]

The dropdown confuses things a little so I'll ignore that for now and assume you've got a cell containing the item selected from the dropdown (Just ask if you need help with that bit)

If you've got your selected item in Cell A1, the list of items in column B and your list of numbers in column C then it's:

=VLOOKUP(A1,B:C,2,false)

This works if you've got nothing else in columns B and C and allows you to extend the list at a later date without having to change the formula, if you want to only look at a specific length of list instead then you can just change the 'B:C' bit to the range you want, the documentation for VLOOKUP should explain everything anyway.

[Feek]

Gotcha, that works providing I type the full name in as it appears in the list, so it's a good start, thanks.

Can I create a dropdown so I can choose from the appropriate items in the list?

[kaiowas]

Yup, easiest option is to not use a dropdown :p

Go to the cell where you want the 'dropdown' go to Data > Validation and select 'list' from the 'allow' box. Then give it the range containing your list of values and make sure you've got "In cell dropdown" checked. When you then go to the cell now a dropdown arrow should come up allowing you to select a value.

One small issue with this is that technically the list of values should be on the same worksheet as the dropdown although you can get around this by using a named range.

[Feek]

You're a star! I've got the list on another sheet so I just selected and named the range and did as above, perfect, thanks - Just as I wanted :)

[kaiowas]

Just a warning that by using a named range you've now lost the ability to add to the list (unless you redefine the named range every time you do so) however if you use a lovely trick known as a dynamic named ranges you can have that back again too:

http://www.ozgrid.com/Excel/DynamicRanges.htm