Solved Formula to evaluate adjacent cell value, and return text

Microsoft Excel 2010 - complete product...
December 13, 2017 at 10:12:32
Specs: Windows 10 Enterprise
I need help please with a formula to evaluate numeric values in Col A and return the specified text associated with the matched range. There are 20 ranges, so I cannot use nested IFs and there are more than 100K rows so I cannot use index matching. Below are the 20 ranges, and associated text that should be autofilled.

Ex: If A1 = 5,500, then B1 should be "Category 2"; if A2 = 20,000, then B2 should be "Category 4"

Below are the ranges and the 'Category x' text to autofill for each:

"1 - 4,999" "Category 1"
"5,000 - 9,999" "Category 2"
"10,000 - 14,999" "Category 3"
"15,000 - 19,999" "Category 4"
"20,000 - 24,999" "Category 4"
"25,000 - 29,999" "Category 5"
"30,000 - 34,999" "Category 6"
"35,000 - 39,999" "Category 7"
"40,000 - 44,999" "Category 8"
"45,000 - 49,999" "Category 9"
"50,000 - 54,999" "Category 10"
"55,000 - 59,999" "Category 11"
"60,000 - 64,999" "Category 12"
"65,000 - 69,999" "Category 13"
"70,000 - 74,999" "Category 14"
"75,000 - 79,999" "Category 15"
"80,000 - 84,999" "Category 16"
"85,000 - 89,999" "Category 17"
"90,000 - 94,999" "Category 18"
"95,000 - 99,999" "Category 19"
"100,000+" "Category 20"

Thank you!


See More: Formula to evaluate adjacent cell value, and return text

Reply ↓  Report •

#1
December 13, 2017 at 12:53:48
✔ Best Answer
You didn't say where your data table is located so I'll put a modified version of it starting in C1. With the formula that I am suggesting, you don't need the upper range of each category.

                 C         D
1                  1	Category 1
2              5,000	Category 2
3             10,000	Category 3
4             15,000	Category 4
...
21           100,000    Category 20

Put this in B1:

=VLOOKUP(A1,$C$1:$D$21,2,1)

When the range_lookup argument of VLOOKUP is set to 1 (TRUE), the function works as follows:

"If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned."

For example, If A1 contains 5,467 VLOOKUP will return 5000 and the result will be Category 2.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
December 13, 2017 at 13:15:03
Thank you! This worked great!!

Reply ↓  Report •
Related Solutions


Ask Question