Distribution Grid

[ysherriff]

I have a distribution as below: I have a formula in VBA that looks at a distribution grid and i need the RPF that falls within in it. I don't want to do it via VBA but formula. Can someone help? For instance, if someone has 93%, then for RPF i want it to display 0%. I have attached an excel file as well.

Min Max RPF
0.00% 97.99% 0%
98.00% 99.99% 25%
100.00% 103.99% 100%
104.00% 106.99% 125%
107.00% 109.99% 150%
110.00% 110.99% 200%
111.00% 1000.00% 200%


% RPF
93.00%
106.00%

 

[Luke M]

You'll be happy to learn about the LOOKUP function. With your example, formula in D14 would be:
=LOOKUP(C14,$C$5:$C$11,$E$5:$E$11)
The Max values are not needed, only the lower thresholds for each group. In fact, if you want to delete cells D5:D11 (sliding the RDF values from col E to D), then your formula can get even smaller, to:
=LOOKUP(C14,$C$5:$D$11)

 

[Debraj]

@Luke M .. Just a side note
Lookup has one more hidden feature..

If Result vector was missing then.. Lookup vector return the results from the last Column in Lookup Vector..
=LOOKUP(C14,$C$5:$E$11)

 

[Luke M]

@Luke M .. Just a side note
Lookup has one more hidden feature..

If Result vector was missing then.. Lookup vector return the results from the last Column in Lookup Vector..
=LOOKUP(C14,$C$5:$E$11)

mind....blown!

 

[ysherriff]

Thank both of you and WOW. I need to use lookup more often. Never knew this formula is this powerful. can it be use with other lookup formulas such as index, vlookup, sumproduct, etc.

 

[Luke M]

Yep, I suppose you could. The nice thing about functions is that as long as the output of one is in the expected format of the input of the other, you can next them together. I've seen things like:
=VLOOKUP(SomeValue,INDEX(BigRange,B12),LOOKUP(OtherValue,LookupTable),FALSE)

Where the INDEX controlled which table to return info for from the VLOOKUP, and the LOOKUP controlled which column. Use your imagination, and go crazy!