find the last change in column and get date value

[kingcashandcarry]

i have a headache today. i am trying to do many things to my projects and cant manage .
but one that stubborn me i am asking your help to solve it
so i have a range with dates and data every row is the next day the data might be steady or might be changed but someday will be the last change
so which day is this? my question is to find the date to the last change of value in range datalookup changed value.xlsx

[AliGW]

Try this:

=LOOKUP(2,1/(B:B<>""),A:A)

You'll need semi-colons instead of commas.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E	F	G	H	I
1	date	data						#VALUE!	14/01/2017
2	01/01/2017	121
3	02/01/2017	121
4	03/01/2017	122
5	04/01/2017	123
6	05/01/2017	123
7	06/01/2017	123
8	07/01/2017	123
9	08/01/2017	123
10	09/01/2017	123
11	10/01/2017	123
12	11/01/2017	123
13	12/01/2017	123
14	13/01/2017	124
15	14/01/2017	124

Sheet: F????1

[kingcashandcarry]

thank you for the fast responce
but the result was the last value in a column
i dont want this. the result must be 13/01/2017 the day that the value changes from 123 to 124 and is the last change in b column

[AliGW]

OK. Try this:

=INDEX(A:A,MATCH(LOOKUP(2,1/(B:B<>""),B:B),B:B,0))

[TMS]

Try this:

Please Login or Register  to view this content.

You'll probably need to change commas to semi-colons and Array-Enter the formula . . . commit with Ctrl-Shift-Enter rather than just Enter.

[kingcashandcarry]

thank you aliGW i had to do some adjustments to my workbook but all seems to work perfect
i will try and your TMS !!!
KISSES BYE

[AliGW]

You're welcome.

[TMS]

You're welcome. Thanks for the rep.