Excel formula lookup or v look look up

[kumar1803]

I would like the look for value and assigned a flat% based on value: for example:

columnA. Column B
tier name. A+

flat%. Lookup formula

if column b says "A+" then flat is 1%, if only "A " then 1.5% and so on.

 

[bbott]

Take a look at this example and see if it works for you:

=MATCH(B1,{"A+","A","A-","B+","B","B-"},0)*0.5+0.5

 

[kumar1803]

Thank you.

It did not help. Here is the result I got when i used the formula given:

[TABLE="width: 665"]
<colgroup><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]LOAN VALUES[/TD]
[TD] [/TD]
[TD="colspan: 3"]DEALER RESERVE SUMMARY[/TD]
[/TR]
[TR]
[TD]Loan Amount[/TD]
[TD] [/TD]
[TD="align: right"]25,000.00[/TD]
[TD] [/TD]
[TD]Monthly Payment[/TD]
[TD] [/TD]
[TD]540.32[/TD]
[/TR]
[TR]
[TD]Sell Rate[/TD]
[TD] [/TD]
[TD="align: right"]10.74%[/TD]
[TD] [/TD]
[TD]Number of Payments[/TD]
[TD] [/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]Buy Rate[/TD]
[TD] [/TD]
[TD="align: right"]8.74%[/TD]
[TD] [/TD]
[TD]Total Interest( Buy Rate)[/TD]
[TD] [/TD]
[TD]5,948.59[/TD]
[/TR]
[TR]
[TD]Loan Period in Years[/TD]
[TD] [/TD]
[TD="align: right"]5[/TD]
[TD] [/TD]
[TD]Total Interest (Sell Rate)[/TD]
[TD] [/TD]
[TD]7,419.48[/TD]
[/TR]
[TR]
[TD]Start Date of Loan[/TD]
[TD] [/TD]
[TD="align: right"]3/1/2015[/TD]
[TD] [/TD]
[TD]Total Cost of Loan[/TD]
[TD] [/TD]
[TD]32,419.48[/TD]
[/TR]
[TR]
[TD]Tier[/TD]
[TD] [/TD]
[TD]A+[/TD]
[TD] [/TD]
[TD]Total Reserve[/TD]
[TD] [/TD]
[TD]7,419.48[/TD]
[/TR]
[TR]
[TD]Flat %[/TD]
[TD] [/TD]
[TD="align: right"]100%[/TD]
[TD] [/TD]
[TD]Dealer Reserve[/TD]
[TD] [/TD]
[TD]5,564.61

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 665"]
<colgroup><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]If Tier is " A+" then Flat % would be: 2%
If Tier is "A" then Flat % is 2%
If Tier is "B" then Flat% is 2%
If Tier is "C" then Flat % is 1%
If Tier is "D" then Flat % is 1%

I hope it make sense. Thank you for your help.[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]





[/TD]
[/TR]
</tbody>[/TABLE]

 

[bbott]

Which cell contains the tier?

Assuming it's in C7, try:

=LOOKUP(C7,{"A","A+","B","C","D"},{0.02,0.02,0.02,0.01,0.01})

 

[kumar1803]

Thank you, It worked.

 

[kumar1803]

In the next formula I have Tier as "1","2","3","4" instead of A,A+,B,C. I have applied the same formula but received N/A. Please help

 

[bbott]

I'm not quite clear on the issue, can you post the formula that you tried and the expected results?

 

[Aladin Akyurek]

Which cell contains the tier?

Assuming it's in C7, try:

=LOOKUP(C7,{"A","A+","B","C","D"},{0.02,0.02,0.02,0.01,0.01})

VLOOKUP would be more appropriate for such...

 

[bbott]

VLOOKUP would be more appropriate for such...

How so? Can you use VLOOKUP without an existing table?

 

[Aladin Akyurek]

How so? Can you use VLOOKUP without an existing table?

C7: A-

B1:

=LOOKUP(C7,{"A","A+","B","C","D"},{0.02,0.02,0.02,0.01,0.01})

Yes, we can a hard-coded in VLOOKUP too...

C1:

=VLOOKUP(C7,{"A",0.02;"A+",0.02;"B",0.02;"C",0.01;"D",0.01},2,0)