if then else

[Guest]

I am trying to create a column that will convert a number score into a letter
score based on a range. For instance, if the cell has 720 in it then I want
an A to print in another cell. If the number is 600 then a C should print. I
can get it to print using an IF statement so long as there is only one
option. Any ideas for multiple options? I tried VLOOKUP but that doesn't work.

 

[Bob Phillips]

=VLOOKUP(A1,{720,"A";600,"B"},2,FALSE)

just extend that range or put it into a worksheet range and use that.

--

HTH

RP
(remove nothere from the email address if mailing direct)

I am trying to create a column that will convert a number score into a letter
score based on a range. For instance, if the cell has 720 in it then I want
an A to print in another cell. If the number is 600 then a C should print. I
can get it to print using an IF statement so long as there is only one
option. Any ideas for multiple options? I tried VLOOKUP but that doesn't

work.

 

[Guest]

Thanks Bob. I think that I have it working. Here is a followup question.

I have the column returning the A or B. Now that information along with a
third column needs to be used to give me a rate. So A and 21 is 9.5 but A and
22 is 10.5. The VLOOKUP doesn't seem to like the three dimensions that I
think I will need in my table. Is there another type of formula that will
work. Sometimes I think that it would be easier for MS to give us IF, THEN
and ELSE statements. Thanks again, Russell

 

[Bob Phillips]

Russell,

As there will be multiple A for rates, I would not hold it all in one table
but have two tables and use the results of one lookup to do a lookup into
the second.

However, if you want to continue as you were, assuming the number is in A,
the letter is in B, and the rate is in C1, use

=INDEX(C1:C10,MATCH(20&"A",A1:A10&B1:B10,0))

which is an array formula, so commit it with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I hate to seem like a complete dolt here but I am just not getting this. I
tried what you suggested along with several variations. I put the information
that I have in this email. It is the blank that I am trying to get for this
example of new rate. I think that I just have too many options. I understand
if you want to give up on me. ha ha Russell

COLL TIER NEW RATE
22 B




Collateral Code Tier Rate
25 A 18.00%
25 B 18.00%
25 C 18.00%
25 D 18.00%
22 A 13.50%
22 B 13.50%
22 C 17.50%
22 D 18.00%
26 A 12.50%
26 B 12.50%
26 C 15.50%
26 D 18.00%
27 A 9.50%
27 B 9.50%
27 C 13.50%
27 D 16.50%

 

[Duke Carey]

Where Bob's formula hard-codes a 20 and an A, you want to substitute a
reference to the cells that contain your Collateral code and Tier

Thus, if your spreadsheet has the target Collateral Code and Tier on cells
A2 & B2, and you want the Rate in C2, you'll put the formula in cell C2. Of
course modify the C4:C13 and A4:A13&B4:B13 addresses to match your range of
lookup data.


=INDEX(C4:C13,MATCH(A2&B2,A4:A13&B4:B13,0))

Commit the formula by pressing Shift-Ctrl-Enter

Duke

 

[Guest]

Thanks for the additional input but I give up. I committed the formula as an
array and now I have #VALUE errors, of course. At the rate I am going it will
be easier to do it by hand.

Russell

 

[Guest]

Hello Duke,

I gave it another try this morning. This is what I placed in the cell,
=INDEX('Lookup Table'!C2:C17,MATCH(H2&L2,'Lookup Table'!A2:A17&'Lookup
Table'!B2:B17,0))

I committed it as an array. I get the #VALUE error now. The help seems to be
telling me that I have a constant in the array. Any ideas what this could
mean?

 

[Bob Phillips]

Russell,

Are you sure that you properly committed it as an array (Ctrl-Shift-Enter)?
I created some data to test that exact formula, and the only way I could get
#VALUE was if I just hit enter. If H2 & L2 did not match , I got #N/A.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Yes I committed it as an array. I don't think that I can send it to you
through this or I would.