convert vlookup formula into xlookup for taxes

[sleek12]

I have the following vlookup formula for taxes, is there a way it can be converted to xlookup;-
'=VLOOKUP(B2,Rates,2,TRUE)+VLOOKUP(B2,Rates,3,TRUE)*(B2-VLOOKUP(B2,Rates,1,TRUE))-2400'
the formula is located in C2

tax-rates (1)PAYE.xlsx
	A	B	C	D	E	F
1	Taxable Income	After NSSF	Tax on this income	NHIF	NSSF	Nett pay
2	69,930.00	69,730.00	8,932.00	1,700.00	200	59,098.00
Workings

Cell Formulas
Range	Formula
B2	B2	=A2-200
C2	C2	=VLOOKUP(B2,Rates,2,TRUE)+VLOOKUP(B2,Rates,3,TRUE)*(B2-VLOOKUP(B2,Rates,1,TRUE))-2400
F2	F2	=A2-C2-D2-E2

Named Ranges
Name	Refers To	Cells
Rates	=Mastersheet!$A$1:$C$5	C2

The rates are here;-

tax-rates (1)PAYE.xlsx
	A	B	C
1	0	0	0.1
2	24000	2400	0.15
3	40667	4900	0.2
4	57334	8233	0.25
5
Mastersheet

thanks for your help

 

[jasonb75]

is there a way it can be converted to xlookup

There is, but why would you want to make the formula more complicated if the existing one works?

=XLOOKUP(B2,Mastersheet!$A$1:$A$5,Mastersheet!$B$1:$B$5)+XLOOKUP(B2,Mastersheet!$A$1:$A$5,Mastersheet!$C$1:$C$5)*(B2-XLOOKUP(B2,Mastersheet!$A$1:$A$5,Mastersheet!$A$1:$A$5))-2400

 

[sleek12]

Its not working, am getting

tax-rates (1)PAYE.xlsx
	C
5	#N/A
Workings

Cell Formulas
Range	Formula
C5	C5	=XLOOKUP(B2,Mastersheet!$A$1:$A$5,Mastersheet!$B$1:$B$5)+XLOOKUP(B2,Mastersheet!$A$1:$A$5,Mastersheet!$C$1:$C$5)*(B2-XLOOKUP(B2,Mastersheet!$A$1:$A$5,Mastersheet!$A$1:$A$5))-2400

The main reason am moving away from vlookup(RIP) to newer functions like XLOOKUP,FILTER,UNIQUE,SWITCH,etc

 

[sleek12]

Its worked now after i added -1

tax-rates (1)PAYE.xlsx
	A	B	C	D	E	F
1	Taxable Income	After NSSF	Tax on this income	NHIF	NSSF	Nett pay
2	80,000.00	79,800.00	11,449.50	1,700.00	200	66,650.50
3			0.25
4			11,449.50
5			11,449.50
Workings

Cell Formulas
Range	Formula
B2	B2	=A2-200
F2	F2	=A2-C2-D2-E2
C2	C2	=VLOOKUP(B2,Mastersheet!A1:C5,2,TRUE)+VLOOKUP(B2,Mastersheet!A1:C5,3,TRUE)*(B2-VLOOKUP(B2,Mastersheet!A1:C5,1,TRUE))-2400
C3	C3	=XLOOKUP(B2,Mastersheet!A1:A5,Mastersheet!C1:C5,,-1)
C4	C4	=IF(B2>=Mastersheet!A4,Sheet1!B4)+(B2-Mastersheet!A4)*0.25-2400
C5	C5	=XLOOKUP(B2,Mastersheet!$A$1:$A$5,Mastersheet!$B$1:$B$5,,-1)+XLOOKUP(B2,Mastersheet!$A$1:$A$5,Mastersheet!$C$1:$C$5,,-1)*(B2-XLOOKUP(B2,Mastersheet!$A$1:$A$5,Mastersheet!$A$1:$A$5,,-1))-2400

 

[jasonb75]

The main reason am moving away from vlookup(RIP) to newer functions

That seems like a kind of pointless reason. It is extremely unlikely that the older functions would be removed from future versions of excel so making a formula more complicated than it needs to be for the sake of using a newer function is absolutely ridiculous.

Without knowing the tax rules for your country I can't say for certain, but I'm not entirely convinced that the results of your original formula are correct. (as an example, a figure below 24000 in B2 will give you a negative result which just doesn't seem right).

Most tax formulas use sumproduct(if arrays rather than multiple lookups.

 

[sleek12]

That seems like a kind of pointless reason. It is extremely unlikely that the older functions would be removed from future versions of excel so making a formula more complicated than it needs to be for the sake of using a newer function is absolutely ridiculous.

Without knowing the tax rules for your country I can't say for certain, but I'm not entirely convinced that the results of your original formula are correct. (as an example, a figure below 24000 in B2 will give you a negative result which just doesn't seem right).

Most tax formulas use sumproduct(if arrays rather than multiple lookups.

Yes, you're indeed right, person with income under 24000/- dont pay tax.

Kenyan tax rates