Lookups in Excel - How?

Feek · 09-11-2008, 15:11

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 · 09-11-2008, 18:14

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 · 09-11-2008, 18:24

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 · 09-11-2008, 18:42

Yup, easiest option is to not use a dropdown

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 · 09-11-2008, 18:48

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 · 09-11-2008, 18:54

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

09-11-2008, 15:11	#1
Feek ex SAS Join Date: Jun 2006 Location: JO01ou Posts: 10,062	Lookups in Excel - How? 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. __________________

09-11-2008, 18:14	#2
kaiowas The Stig Join Date: Jul 2006 Location: Fightertown USA Posts: 1,458	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. __________________ Anal Fish Porn

09-11-2008, 18:24	#3
Feek ex SAS Join Date: Jun 2006 Location: JO01ou Posts: 10,062	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? __________________ Last edited by Feek; 09-11-2008 at 18:36.

09-11-2008, 18:42	#4
kaiowas The Stig Join Date: Jul 2006 Location: Fightertown USA Posts: 1,458	Yup, easiest option is to not use a dropdown 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. __________________ Anal Fish Porn

09-11-2008, 18:48	#5
Feek ex SAS Join Date: Jun 2006 Location: JO01ou Posts: 10,062	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 __________________

09-11-2008, 18:54	#6
kaiowas The Stig Join Date: Jul 2006 Location: Fightertown USA Posts: 1,458	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 __________________ Anal Fish Porn