More efficient way to avoid multiple If statements in one formula

[dbarbella]

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

 

[JoeMo]

EDIT: The formula requires col D to be sorted descending as it is here.
Sheet10

	A	B	C	D
1	6000	Account Tiers (Column B)	Low (Column C)	High (Column D)
2	Tier 2	Tier 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

Cell Formula A2 =INDEX($B2:$B5,MATCH(A1,$D$2:$D$5,-1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[dbarbella]

Thanks so much Joe,

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

 

[JoeMo]

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.

 

[dbarbella]

Ah I see.
Thanks again for the quick help.... very very appreciated