More efficient way to avoid multiple If statements in one formula

dbarbella

New Member
Joined
Jun 6, 2017
Messages
32
Hey Gang.
I'm wondering what the most efficient approach would be

On another sheet, I have a value
I want to return the Account Tier Name based on what range that value falls between

So let's say I have a cell that I enter the value $2,500 (Let's call that Cell A1)

I need it to scan the below table:
<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>
Account Tiers (Column B)Low (Column C)High (Column D)
Tier 1$10,000$9,999,999,999
Tier 2$4,000$9,999
Tier 3$2,000$3,999
Tier 4$0$1,999

<colgroup><col style="width: 169px"><col width="126"><col width="126"><col width="126"></colgroup><tbody>
</tbody>

And Return "Tier 3" in the cell holding the formula

Entering $6,000 would return Tier 2 (and so on).

I'm sure I can write a long formula with 4 different IF statements, but wondered if there was a more efficient way?
Or if there's not... what is the best syntax to go with?

Thanks so much
-Dave
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,392
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
EDIT: The formula requires col D to be sorted descending as it is here.
Sheet10


ABCD
16000Account Tiers (Column B)Low (Column C)High (Column D)
2Tier 2Tier 1$10,000 $9,999,999,999
3
Tier 2$4,000 $9,999
4
Tier 3$2,000 $3,999
5
Tier 4$0 $1,999

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:165px;"><col style="width:105px;"><col style="width:109px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
A2=INDEX($B2:$B5,MATCH(A1,$D$2:$D$5,-1))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:

dbarbella

New Member
Joined
Jun 6, 2017
Messages
32
Thanks so much Joe,

for my own education, why does Col D need to be sorted descending?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,392
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thanks so much Joe,

for my own education, why does Col D need to be sorted descending?
You are welcome.

Because you are not looking for an exact match the formula tries to find the best match in col D that is less than or equal to the A1 value. if col D is not sorted descending the formula may return an erroneous result.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,062
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top