Microsoft Excel 2010 - complete product...

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!

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 20Put this in B1:

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

When the

range_lookupargument of VLOOKUP is set to 1 (TRUE), the function works as follows:"If

range_lookupis 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

Thank you! This worked great!!

Ask Your Question

Weekly Poll