Vlookup a column with duplicate value, return the number from the last row

[Ironhan]

Hi all,

I've got a table with 4 columns(B~D), Item, Store, and Time.

In Column E, I want to write a function that looks up the value from "Item" and "Store", then returns the value of "Time" of the last row where both "Item" and "Store" values are matched.

So for example, E3~E9 will have the value of 12:30 since that is the last row of "Apple" and "CA".

I apologize for the clumsy explanation, please see the attached image for your reference.

Any help will be greatly appreciated.

Thanks

 

[etaf]

you are showing the max(time) did you mean last row or max time - was not sure if just a coincidence it was maximum time and also the last row

 

[Ironhan]

you are showing the max(time) did you mean last row or max time - was not sure if just a coincidence it was maximum time and also the last row

Yes, it is the maximum(latest) time.

 

[etaf]

are you still using 2016 version ,
otherwise a MAXIFS() would work - available from 2019 version
=MAXIFS($D$3:$D$21,$B$3:$B$21,B3,$C$3:$C$21,C3)

Aggregate - added in excel 2010 , so should work in 2016 version
=AGGREGATE(14,6,$D$3:$D$21/(($B$3:$B$21=B3)*($C$3:$C$21=C3)),1)

MAX(IF
=MAX(IF(($B$3:$B$21=B3)*($C$3:$C$21=C3),$D$3:$D$21))

Note i have added a value that is the max time - BUT NOT the last row

Book7
	A	B	C	D	E	F	G
1
2					MAXIFS()	AGGREGATE()	MAX(IF(
3		Apple	CA	12:00	12:30	12:30	12:30
4		Apple	CA	12:05	12:30	12:30	12:30
5		Apple	CA	12:10	12:30	12:30	12:30
6		Apple	CA	12:15	12:30	12:30	12:30
7		Apple	CA	12:20	12:30	12:30	12:30
8		Apple	CA	12:25	12:30	12:30	12:30
9		Apple	CA	12:30	12:30	12:30	12:30
10		Apple	NY	17:00	17:20	17:20	17:20
11		Apple	NY	17:05	17:20	17:20	17:20
12		Apple	NY	17:10	17:20	17:20	17:20
13		Apple	NY	17:15	17:20	17:20	17:20
14		Apple	NY	17:20	17:20	17:20	17:20
15		Banana	DE	9:05	18:00	18:00	18:00
16		Banana	DE	9:10	18:00	18:00	18:00
17		Banana	DE	9:15	18:00	18:00	18:00
18		Banana	DE	9:20	18:00	18:00	18:00
19		Banana	DE	18:00	18:00	18:00	18:00
20		Banana	DE	9:30	18:00	18:00	18:00
21		Banana	DE	9:35	18:00	18:00	18:00
Sheet1

Cell Formulas
Range	Formula
E3:E21	E3	=MAXIFS($D$3:$D$21,$B$3:$B$21,B3,$C$3:$C$21,C3)
F3:F21	F3	=AGGREGATE(14,6,$D$3:$D$21/(($B$3:$B$21=B3)*($C$3:$C$21=C3)),1)
G3:G21	G3	=MAX(IF(($B$3:$B$21=B3)*($C$3:$C$21=C3),$D$3:$D$21))