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

Ask Your Question

Weekly Poll

Do you think state sales tax should be charged in the United States on all Internet sales?

Discuss in The Lounge

Poll History