# 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

#1
December 13, 2017 at 12:53:48
 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.