Need a Formula that Categorizes Cell Value Based On Value Range

**mbibanker1** · 10-03-2017, 01:17 PM

I have a prospect list of various companies in our area and I am trying to categorize them by their annual revenue. Based on the companies annual revenue value, I want to put a formula in an adjacent cell that will label thje revenue value as one of the following:

$1 - $5MM
$5 - $10MM
$10 - $25MM
$25 - 50MM
I have this set up on another sheet (see below), but am trying to figure out how to make a formula that will make use of this table. I'm thinking something along the lines of a VLOOKUP funciton, but it has to be more complex than that.
Revenue Threshold Category
1 $1 - $5MM
5 $5 - $10MM
10 $10 - $25MM
25 $25 - 50MM
50

**63falcondude** · 10-03-2017, 01:29 PM

With 1 in A2, try this in B2:

=LOOKUP(A2,{1,5,10,25},{"$1 - $5MM","$5 - $10MM","$10 - $25MM","$25 - 50MM"})

Or you can reference your table like this:

=LOOKUP(A2,C1:C4,D1:D4) adjusting the ranges as needed.

**CAntosh** · 10-03-2017, 01:30 PM

If your threshold values are in ascending order, you can use INDEX/MATCH with a "Match Type" of 1 on the threshold values to return the proper category. In the attached sample, the threshold table is in G2:H6, the company revenue is in B2, and the lookup in C2 is:

=INDEX($H$2:$H$6,MATCH($B2,$G$2:$G$6,1))

Fill down for other companies. Take a look at the attachment to see if it'll do the trick: