How do I look up a value in a range?

[smess5]

I have a column of values. I want to assign each value a "group code". The groups are defined by a range ie. betw 0-5 is group A, 6-10 is group B etc. How do I do a lookup that finds a number between two numbers in two columns?

here's an example of what I would need to find a group for in the database below:

value to lookup

3
12
21


Group
A 0 5
B 6 10
C 11 15
D 16 20
E 21 25
F 26 30



Thanks so much,
Laura

 

[nbrcrunch]

1. you do not need the upper range numbers in your lookup grid
2. reorg your Group lookup list so the number is first and the letter is 2nd
3. use: =vlookup(SourceNbr,LookupGroup,2,false)

where SourceNbr number is the number you want to assign a code,
LookupGroup is the grid containing the code.

See Excel help for further explanation of the VLOOKUP() function.

 

[NBVC]

1. you do not need the upper range numbers in your lookup grid
2. reorg your Group lookup list so the number is first and the letter is 2nd
3. use: =vlookup(SourceNbr,LookupGroup,2,false)

where SourceNbr number is the number you want to assign a code,
LookupGroup is the grid containing the code.

See Excel help for further explanation of the VLOOKUP() function.

I think, probably better to use True or 1 in the Vlookup statement, the False will return #NA if exact match not found and the OP is not looking necessary for exact match, but closest match.

=vlookup(SourceNbr,LookupGroup,2,true)

 

[nbrcrunch]

You are right. I am just so used to checking for exact that I totally disregarded the actual test case I did before posting. My actual was:


=vlookup(SourceNbr,LookupGroup,2)

completely dropping the last option.