Matching a single value, with a value category.

[classic3283]

I'm trying to figure out how I can easily match a list of numbers into a set
category.
For example. I have the number 11. I have the categories 1-10, 11-19,
20-29, ... and so on. Next to the value of 11, I want it to list the
category it's in (11-19). And for the number 86 below it, and the number 25
below that. Can anyone help me figure this out. I tried using VLookUp but
to no avail. Thanks in advance.

 

[bpeltzer]

You're on the right track with vlookup... Create a two-column table, where
the first column lists the minimum value in each category, in ascending
order: 1 in the first row, then 11, 20, etc. In the second column list the
category you want to display: 1-10 in the first row, 11-19 in the second,
20-29, etc.
Then if the value you're categorizing is in cell A1,
=vlookup(A1,table_range,2) will return the category.

 

[Arvi Laanemets]

Another way is using MATCH - like this:
=IF(A1<1,"",MATCH(A1,{1;12;20;30},1))
(add border elements into formula accordingly your own needs)

When you want instead category nunber some other values returned, then you
have to expand the formula - a couple of examples:
=IF(A1<1,"","kat" & MATCH(A1,{1;12;20;30},1))
or
=IF(A1<1,"",CHOOSE(MATCH(A1,{1;12;20;30},1),"First category","Second
category","Third category","Fourth category"))
(you can have up to 29 different selection values in CHOOSE function, unless
you refer to some cell range)

You also can have border values in some table, and refer to table range in
MATCH function, like
=IF(A1<1,"",MATCH(A1,$J$2:$J$5,1))
or when the lookup range is defined as dynamic named range
=IF(A1<1,"",MATCH(A1,MatchBorders,1))
, but mostly VLOOKUP will be more appropriate then.


Arvi Laanemets