ESIC Calculation by vlookup [SOLVED]

[chintan_1985]

I have the following data of basic payment range (in column A) and ESIC decuctible in column B:

[pre]

A             B
1  0-1000         50
2  1001-3000      100
3  3001-5000      150
4  >5000          300
Employee      Basic         ESIC Decuctible
Raj           3000
Ramesh        5010
Raviraj       800

[/pre]
I want ESIC decuctible amount by using vlookup(). Can you suggest how? I am totally blank this time...why I don't know

 

[Hui]

Chintan


Instead of putting ranges like 0-1000 simply put 0

then use a VLookup


eg:

[pre]

0	50
1001	100
3001	150
5000	300	

Employee	Basic	ESIC Decuctible
Raj		3000	100
Ramesh		5010	300
Raviraj		1000	50

[/pre]
The Vlookup will be : =VLOOKUP(B8,$A$1:$B$4,2)

 

[chintan_1985]

Hi,


Thanks you for the promopt reply,


In this case where the basic payment is not excactly the same as per ESIC scale, then vlookup() will not working.


I would request you to guide me to formulate vlookup() for the range function.


Thanks sir!!!

 

[Faseeh]

Hi chintan_1985,


Hui's formula will work for the range. eg for this:

[pre]

A	B	C	Value
1	0	1000	50
2	1001	3000	100
3	3001	5000	150
4	5001	10000	300[/pre]

=VLOOKUP(120,$B$2:$D$5,3)

..replace 120 for any value between 0-10000.


Regards,

 

[chintan_1985]

Hi Faseeh,


Great Sir!!!


Thanks for the help.


Just one query, that could we not cap the upper limit (10,000)

 

[Faseeh]

Hi chintan_1985,


If cap mean that if you enter a value beyond 10000 you should get nothing then try following:

=IF(E1>10000,"-",VLOOKUP(E1,$B$2:$D$5,3))

Regards,

 

[chintan_1985]

ohhhh...


I could not think in this direction...


thanks sir