complicated if then formula...need help

[LincAg]

Here's what I'm looking for: A way to have a value generated below reflect the letter that corresponds with a dollar tier listed below. (the tiers change, the letters are fixed.)

Tier Low High
A $1,500.00 $4,499.99
B $4,500.00 $7,499.99
C $7,500.00 $16,999.99
D $17,000.00 $26,499.99
E $26,500.00 $9,999,999.00

The Tier letters in the first column are set, the users input the tier dollar amounts.

Below there is a cell that spits out a total amount and I want it to display the letter for the corresponding tier. No matter what I do I can't get it to work.

Basically I want it to say something like this:
If E37 is equal to or greater than the cell that corresponds with 1500 but less than the cell that corresponds with 4500 than display the cell that shows Tier A, If it's equal to or greater than 4500 and less than the cell with 7500 than show B, etc. so it takes that one number and spits out what letter tier and if its not in any tier it shows no value or -.

Is this possible and what's the easiest way?

[Toppers]

Try:

Table in A1:C6

Place this in required cell:

=IF(ISERROR(INDEX(A2:A6,MATCH(E37,$B$2:$B$6,1))),"",INDEX(A2:A6,MATCH(E37,$B$2:$B$6,1)))

HTH

"LincAg" wrote:

>
> Here's what I'm looking for: A way to have a value generated below
> reflect the letter that corresponds with a dollar tier listed below.
> (the tiers change, the letters are fixed.)
>
> Tier Low High
> A $1,500.00 $4,499.99
> B $4,500.00 $7,499.99
> C $7,500.00 $16,999.99
> D $17,000.00 $26,499.99
> E $26,500.00 $9,999,999.00
>
> The Tier letters in the first column are set, the users input the tier
> dollar amounts.
>
> Below there is a cell that spits out a total amount and I want it to
> display the letter for the corresponding tier. No matter what I do I
> can't get it to work.
>
> Basically I want it to say something like this:
> If E37 is equal to or greater than the cell that corresponds with 1500
> but less than the cell that corresponds with 4500 than display the cell
> that shows Tier A, If it's equal to or greater than 4500 and less than
> the cell with 7500 than show B, etc. so it takes that one number and
> spits out what letter tier and if its not in any tier it shows no value
> or -.
>
> Is this possible and what's the easiest way?
>
>
> --
> LincAg
> ------------------------------------------------------------------------
> LincAg's Profile: http://www.excelforum.com/member.php...o&userid=34798
> View this thread: http://www.excelforum.com/showthread...hreadid=545542
>
>

[Bob Phillips]

Create a table in M1:N5 like so
1500 A
4500 B
7500 C
17000 D
26500 E


then use

=VLOOKUP(E37,$M$1:$N$5,2)

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"LincAg" <[email protected]> wrote in
message news:[email protected]...
>
> Here's what I'm looking for: A way to have a value generated below
> reflect the letter that corresponds with a dollar tier listed below.
> (the tiers change, the letters are fixed.)
>
> Tier Low High
> A $1,500.00 $4,499.99
> B $4,500.00 $7,499.99
> C $7,500.00 $16,999.99
> D $17,000.00 $26,499.99
> E $26,500.00 $9,999,999.00
>
> The Tier letters in the first column are set, the users input the tier
> dollar amounts.
>
> Below there is a cell that spits out a total amount and I want it to
> display the letter for the corresponding tier. No matter what I do I
> can't get it to work.
>
> Basically I want it to say something like this:
> If E37 is equal to or greater than the cell that corresponds with 1500
> but less than the cell that corresponds with 4500 than display the cell
> that shows Tier A, If it's equal to or greater than 4500 and less than
> the cell with 7500 than show B, etc. so it takes that one number and
> spits out what letter tier and if its not in any tier it shows no value
> or -.
>
> Is this possible and what's the easiest way?
>
>
> --
> LincAg
> ------------------------------------------------------------------------
> LincAg's Profile:
http://www.excelforum.com/member.php...o&userid=34798
> View this thread: http://www.excelforum.com/showthread...hreadid=545542
>

[LincAg]

That first one worked out great. I'll play with the second one later. Great job! - Thanks!!!