Fetch last drawn salary month in excel

[govind_bhuse]

I have a list of employees and their month wise salary drawn. I need the "last salary drawn" & "month name" for all employees. The needed result is given in column H & I. Can we do it using excel formula ?

 

[Habtest]

See if it works:

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Jan	Feb	Mar	Apr	May	Jun	Last Month	Last Salary Drawn
2		150	550	550	400			Apr	400
3		800	200			100	450	Jun	450
4		100		200		200		May	200
5		800	250					Feb	250
6		500	550	550	550	100	450	Jun	450
Sheet4

Cell Formulas
Range	Formula
H2:H6	H2	=INDEX($B$1:$G$1,1,AGGREGATE(14,6,COLUMN($B2:$G2)+COLUMN($B2:$G2)/($B2:$G2>0),1)/2-1)
I2:I6	I2	=XLOOKUP(H2,$B$1:$G$1,B2:G2,0)

 

[fadee2]

try the following
Last Salary Drawn

=LOOKUP(2,1/(B2:G2<>""),B2:G2)

Last Month

=INDIRECT(ADDRESS(1,LOOKUP(2,1/(A2:G2<>""),COLUMN(2:2)),4))

Book1
	A	B	C	D	E	F	G	H	I
1	Employees	Jan	Feb	Mar	Apr	May	June	Last Month	Lat Salary
2	A	100	600	800	100			Apr	100
3	B	200	500			300	200	June	200
4	C	300		500		600		May	600
5	D	250	400					Feb	400
6	E	500	100	500	350	500	500	June	500
Sheet1

 

[govind_bhuse]

See if it works:

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Jan	Feb	Mar	Apr	May	Jun	Last Month	Last Salary Drawn
2		150	550	550	400			Apr	400
3		800	200			100	450	Jun	450
4		100		200		200		May	200
5		800	250					Feb	250
6		500	550	550	550	100	450	Jun	450
Sheet4

Cell Formulas
Range	Formula
H2:H6	H2	=INDEX($B$1:$G$1,1,AGGREGATE(14,6,COLUMN($B2:$G2)+COLUMN($B2:$G2)/($B2:$G2>0),1)/2-1)
I2:I6	I2	=XLOOKUP(H2,$B$1:$G$1,B2:G2,0)

Hi Habtest,

Thanks for your reply,

Using your formulas, I was able to fetch the month but not able to fetch the "Last Drawn Salary", that is may be due to the xlooup formula which works in office 365 only and I am working on office 2019.

 

[govind_bhuse]

try the following
Last Salary Drawn

=LOOKUP(2,1/(B2:G2<>""),B2:G2)

Last Month

=INDIRECT(ADDRESS(1,LOOKUP(2,1/(A2:G2<>""),COLUMN(2:2)),4))

Book1
	A	B	C	D	E	F	G	H	I
1	Employees	Jan	Feb	Mar	Apr	May	June	Last Month	Lat Salary
2	A	100	600	800	100			Apr	100
3	B	200	500			300	200	June	200
4	C	300		500		600		May	600
5	D	250	400					Feb	400
6	E	500	100	500	350	500	500	June	500
Sheet1

Hi Fahad,

Thank you very much for the solution you provided, it has worked like a charm and resolved all my queries, the main thing is... it works in office 2019, which I am currently using. I received solution with other formulas like xlookup, which works in office 365 only and it is not working in office 2019.

 

[fadee2]

You are welcome govind.
Glad it worked the way needed.

Thanks for the feedback.

 

[Dave Patton]

Let_a.xlsm
	A	B	C	D	E	F	G	H	I
1	Employees	Jan	Feb	Mar	Apr	May	Jun	Last Month data in row	Last Amt
2	A	100	600	800	100			Apr	100
3	B	200	500			300	200	Jun	200
4	C	300		500		600		May	600
5	D	250	400					Feb	400
6	E	500	100	500	350	500	500	Jun	500
2e

Cell Formulas
Range	Formula
H2:H6	H2	=LOOKUP(2,1/(A2:G2>0),A$1:G$1)
I2:I6	I2	=LOOKUP(2,1/(B2:G2<>""),B2:G2)