CODEWITHSUNDEEP
google-sheets
TomJones999
TomJones999

Reputation: 853

Assign a tier to each price in a spreadsheet

Let's say I have a price list with several hundred entries. I would like to create price-based categories, such as "Under $ 100", "$ 101 to $ 500", "$ 501 to $ 1000", and "Over $ 1000".

Price   Formula   Tier
$  74    ????     Under $ 100  
$ 199    ????     $ 101 to $ 500
$ 745    ????     $ 501 to $ 1000

I know there's a way to do this in Google Sheets, but can't really figure out how. Is there a more elegant solution than nested IF statements?

Upvotes: 1

Views: 1831

Answers (1)

user6655984
user6655984

Reputation:

VLOOKUP is good for this. Key point:

If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search column are greater than the search key, #N/A is returned.

So, if the table below is in F:G columns, then =vlookup(A2, F1:G4, 2) will return the price range for A2. And =arrayformula(vlookup(A2:A, F:G, 2)) will do it for all data at once.

+------+--------------+
|    0 | under 100    |
|  100 | 100 to 499   |
|  500 | 500 to 999   |
| 1000 | 1000 or more |
+------+--------------+

Upvotes: 2

Related Questions