return a value from range referencing value using formula in excel

[ankitloha123]

Hi Chandoo,


I am Ankit. This site is awesome and provides variety segments of information in excel.

I need to your help to get the value form range referencing value.

Eg: AP state parameters are like below

Rs Amount

<=5000 50

<=6000 80

<=10000 100

<=15000 150

>15000 200


we i have my data with Rs. amount like

6,780

7,020

9,300

5,400

12,500

15,000

23,000


the formula has to refer parameters and return the amount.

Like this i have to do it for different states.


Please refer the below link for reference.

http://www.ziddu.com/download/21167720/Formularequired.xlsx.html


Your cooperation in this regard highly appreciated.


Ankit.

 

[Faseeh]

Hi ankitloha123,


Welcome to the forums!!


Replace data in col D of your file like below:

[pre]

5000
6000
10000
15000
15001

10000
15000
20000
20000

8000
15000
15000

5000
8000
15000
20000
20000[/pre]

i.e remove greater then and equal to sign and use this:


=IFERROR(VLOOKUP(J3,INDIRECT("D"&MIN(IF($C$3:$C$22=I3,ROW($C$3:$C$22)))&":E"&MAX(IF($C$3:$C$22=I3,ROW($C$3:$C$22)))),2),0)

Press Ctrl+Shift+Enter and drag down.


Regards,

 

[ankitloha123]

Hi Faseeh,


Thanks a ton for the support.

It really helped me out in reducing the time spent on tasks.


Regards,

Ankitloha