MAX date for the Issued Item

[binoyfalcon]

Dear Experts,
Thanks for the help in advance. Attached is the document for support.

REPORT SHEET - I need last issued selected item for the Employee.

 

[vletm]

binoyfalcon​

All items will have same LATEST ISSUED DATE; Your data has zeros.
=MAXIFS(Data!H2:H9,Data!A2:A9,Report!B1)

 

[AliGW]

Not sure what you are expecting (no expected results in your workbook), but try these:

=MAXIFS($H$2:$H$9,$A$2:$A$9,$A2)

=LOOKUP(2,1/($B2:$G2>0),$B$1:$G$1)

=LET(a,DROP(REDUCE("",$A$2:$A$9,LAMBDA(x,y,VSTACK(x,BYCOL(FILTER($B$2:$G$9,$A$2:$A$9=y),LAMBDA(c,SUM(c)))))),1),BYROW(a,LAMBDA(r,LOOKUP(2,1/(r>0),B1:G1))))

The third will work only in Excel 365.

EDIT: I did not see the other worksheet - sorry.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Employee No.​	Item 1​	Item 2​	Item 3​	Item 4​	Item 5​	Item 6​	ISSUED DATE​	Max Date	Last Item by row​	Last item by employee​
2​	16736​	2​	1​	1​	1​	1​	0​	15/Jun/24​	15/06/2024​	Item 5	Item 5
3​	16617​	1​	1​	1​	1​	1​	1​	1/Jan/24​	30/03/2024​	Item 6	Item 6
4​	16424​	2​	1​	1​	1​	1​	0​	11/Jun/24​	11/06/2024​	Item 5	Item 5
5​	16619​	1​	1​	1​	1​	1​	0​	20/Jun/24​	20/06/2024​	Item 5	Item 5
6​	16425​	2​	1​	1​	1​	1​	1​	20/Jun/24​	20/06/2024​	Item 6	Item 6
7​	16617​	1​	1​	0​	1​	0​	0​	10/Feb/24​	30/03/2024​	Item 4	Item 6
8​	16617​	4​	1​	1​	1​	1​	1​	20/Feb/24​	30/03/2024​	Item 6	Item 6
9​	16617​	0​	1​	1​	1​	0​	1​	30/Mar/24​	30/03/2024​	Item 6	Item 6

Sheet: Data

 

[binoyfalcon]

In report sheet I select Employee No. & select one Item drop down list , need last date of the item issued to the Emplyee 16617.

 

[binoyfalcon]

Thank you for your support.

In report sheet i choose one Emp. no and select one item from drop down list, last date of the selected issued. in B6:B9

 

[vletm]

binoyfalcon​

You sent same file again ... hmm?
Did You skip #2 reply?

 

[binoyfalcon]

binoyfalcon​

You sent same file again ... hmm?
Did You skip #2 reply?

Thank you,

I choose one Emp. (Employee No.) and select one item from dropdown list, which date we issued the selected item for the Employee (Emp No.)

 

[vletm]

binoyfalcon​

Seems that You skipped that formula, which gives results as You've written.
Take care.

 

[binoyfalcon]

binoyfalcon​

Seems that You skipped that formula, which gives results as You've written.
Take care.

=MAXIFS(Data!H2:H9,Data!A2:A9,Report!B1) this is not working.

1. Select Emp No.
2. Choose the Item
3 cell B6 - Last date of the Item issued to this Employee - 16617.

 

[vletm]

binoyfalcon​

With My Excel-version it works as below...

Only You could know - which version of Excel do You have or use?

 

[binoyfalcon]

binoyfalcon​

With My Excel-version it works as below...
View attachment 87579
Only You could know - which version of Excel do You have or use?

office proffessional Plus 2013

 

[vletm]

binoyfalcon​

You (too) should learn to answer to questions.
Here one my way sample to get two versions of results.
( with '< mode' can choose version )

 

[binoyfalcon]

binoyfalcon​

You (too) should learn to answer to questions.
Here one my way sample to get two versions of results.
( with '< mode' can choose version )

Thank you....

It's working