How to find the last matching value or before the last one?

[stoicy]

Hello All

I have some items in A:A range and a Date in B:B Range
Some items are repeated for example Eggs, Bread…etc.
In cell M1 I want to find the last matching value (which I put in cell L1 – for example I put Eggs)
And how to find the date related to last matching value (eggs) and to put the date in Cell N1.

Another question: what I need to change in the formula given to find the value before the last (if I put the same item eggs) and its related date.
Example:
A1 B1
1 Eggs 12/3/2016
1 Bread 15/3/2016
1 Eggs 17/3/2016
1 Fish 18/3/2016
1 Eggs 19/3/2016
1 Fish 19/3/2016
-------------------------------
The answer will be:
Last value (for eggs) =
A1 B1
Eggs 19/3/2016
-------------------------------
Value before the last (for eggs) =
A1 B1
1 Eggs 17/3/2016
-------------------------------

I hope it is clear

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E
1	Eggs	3/12/2016		Eggs	3/19/2016
2	Bread	3/15/2016			3/17/2016
3	Eggs	3/17/2016
4	Fish	3/18/2016
5	Eggs	3/19/2016
6	Fish	3/19/2016
7	------	------	------	------	------

This array formula** entered in E1:

=IFERROR(LARGE(IF(A$1:A$6=D$1,B$1:B$6),ROWS(E$1:E1)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

Copy down to E2

[stoicy]

Thank you Tony Valko

I try it but it is not working even I Hold down both the CTRL key and the SHIFT key
then hit ENTER but no result shown

[mehmetcik]

enter this into M1 using ctrl shift enter.

It will find the last but one occurrence.

To find the last occurrance change the 2 to a one.

Please Login or Register  to view this content.

[stoicy]

Thank you mehmetcik

it is working

[Tony Valko]

Here's a sample file that demonstrates this.

[stoicy]

thank you Tony Valko, I really appreciate your help and support

[Tony Valko]

You're welcome!