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>[TABLE="width: 0"]
<colgroup><col style="width: 169px"><col width="126"><col width="126"><col width="126"></colgroup><tbody>[TR]
[TD="bgcolor: #C9DAF8"]Account Tiers (Column B)[/TD]
[TD="bgcolor: #C9DAF8"]Low (Column C)[/TD]
[TD="bgcolor: #C9DAF8"]High (Column D)[/TD]
[TD="bgcolor: #C9DAF8"][/TD]
[/TR]
[TR]
[TD]Tier 1[/TD]
[TD="align: right"]$10,000[/TD]
[TD="align: right"]$9,999,999,999[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Tier 2[/TD]
[TD="align: right"]$4,000[/TD]
[TD="align: right"]$9,999[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Tier 3[/TD]
[TD="align: right"]$2,000[/TD]
[TD="align: right"]$3,999[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Tier 4[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$1,999[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

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
Tier 2	Tier 1
	Tier 2
	Tier 3
	Tier 4

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]6000[/TD]
[TD="bgcolor: #ccccff"]Account Tiers (Column B)[/TD]
[TD="bgcolor: #ccccff"]Low (Column C)[/TD]
[TD="bgcolor: #ccccff"]High (Column D)[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]$10,000 [/TD]
[TD="align: right"]$9,999,999,999 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]$4,000 [/TD]
[TD="align: right"]$9,999 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]$2,000 [/TD]
[TD="align: right"]$3,999 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]$0 [/TD]
[TD="align: right"]$1,999 [/TD]

</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