Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching a single value, with a value category.
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching a single value, with a value category.
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. "classic3283" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching a single value, with a value category.
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 "classic3283" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing single pages from multiple worksheets in a single print job | Excel Discussion (Misc queries) | |||
Query on Category & Sub-category | Excel Discussion (Misc queries) | |||
Combine non-matching dates in single chart | Charts and Charting in Excel | |||
Find Multiple instances of Single Criterion in Row & Return To a Single Col | Excel Worksheet Functions | |||
Matching data and linking it to the matching cell | Links and Linking in Excel |