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?

 

[Guest]

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

 

[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]

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