Lookup latest item with index match

[Bozo the C]

Hello a good day!


I would like to look up values in a table with multiple criteria (2).
Then i want to retun the item with the latest start date.


Could you please help me with this?

	A	B	C	D	E	F	G	H
1
2
3		ID	Product	Value	Start
4		1	111	10	1-jan-16		ID	Result
5		1	222	5	1-jan-16		1	5
6		1	222	5	1-jun-16		2	5
7		2	111	5	1-jan-16		3	8
8		2	222	5	1-jan-16
9		2	222	7	1-mei-16
10		2	222	5	1-okt-16
11		3	111	10	1-jan-16
12		3	111	7	1-mei-16
13		3	222	8	1-jan-16

<tbody>
</tbody>

​

I want the value of product 222 with the latest start date in cell H5, H6, etc. for each ID.

Cell	Formula
H5	{=INDEX($D$4:$D$13,MATCH(G5&222,$B$4:$B$13&$C$4:$C$13,0))}
H6	{=INDEX($D$4:$D$13,MATCH(G6&222,$B$4:$B$13&$C$4:$C$13,0))}
H7	{=INDEX($D$4:$D$13,MATCH(G7&222,$B$4:$B$13&$C$4:$C$13,0))}

<tbody>
</tbody>

 

[AhoyNC]

Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER (command-return on MAC).

Excel Workbook
	A	B	C	D	E	F	G	H
1	*	*	*	*	*	*	*	*
2	*	*	*	*	*	*	*	*
3	*	ID	Product	Value	Start	*	*	*
4	*	1	111	10	1-Jan-16	*	ID	Result
5	*	1	222	5	1-Jan-16	*	1	5
6	*	1	222	5	1-Jun-16	*	2	5
7	*	2	111	5	1-Jan-16	*	3	8
8	*	2	222	5	1-Jan-16	*	*	*
9	*	2	222	7	1-Mar-16	*	*	*
10	*	2	222	5	1-Oct-16	*	*	*
11	*	3	111	10	1-Jan-16	*	*	*
12	*	3	111	7	1-Mar-16	*	*	*
13	*	3	222	8	1-Jan-16	*	*	*
Sheet

 

[Bozo the C]

Try:

It's doing exactly what i was looking for.
Thank you very much!

 

[AhoyNC]

You're welcome. Thanks for the feedback

 

[bosco_yip]

In H5, non-array formula copy down:

=LOOKUP(9.99E+307,D$4:D$13/(B$4:B$13=G5)/(C$4:C$13=222))

Regards

 

[Bozo the C]

Hello Bosco, thanks, your formula is interesting. But it's looking voor the last record. I want the latest record.
I think you need MAX for finding the biggest date (column E).