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]

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)

I see.

So in this case, would you recommend VLOOKUP over LOOKUP because LOOKUP will continue to display 0.01 when anything greater than D (i.e. E, F...) is entered? Or is there another reason/advantage?

 

[Aladin Akyurek]

I see.

So in this case, would you recommend VLOOKUP over LOOKUP because LOOKUP will continue to display 0.01 when anything greater than D (i.e. E, F...) is entered? Or is there another reason/advantage?

Yes, we have an exact match situation, so VLOOKUP with match-type set to 0 is preferable (or Index\Match with Match set up to effect an exact match).

If you would insist on Lookup because the match is sorted in ascending order...

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

but there is no need to play this game for such a small match range.

 

[bbott]

Yes, we have an exact match situation, so VLOOKUP with match-type set to 0 is preferable (or Index\Match with Match set up to effect an exact match).

If you would insist on Lookup because the match is sorted in ascending order...

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

but there is no need to play this game for such a small match range.

Makes sense. Thanks for the insight Aladin.