find the last Occurrence in a table and return the value on the cell above it

[linkn00]

Hello,

I am struggling to figure out how to find the last Occurrence in a table and return the value on the cell above it with simple Excel formula. Please help
For example: i want to find the last occurrence for "a" in the following table, it would return c

a	b	a
a	a	c
a	b	a

 

[CA_Punit]

As you have marked bold in 3rd column so

Book1
	H	I	J	K
1	a	b	a
2	a	a	c
3	a	b	a
4
5
6
7
8
9
10	c
11
12
13
Sheet3

Cell Formulas
Range	Formula
H10	H10	=INDEX(J:J,MATCH("ZZZZZZZZZZZ",J:J)-1)

 

[mamady]

Alternatively, if you need an interactive formula to lookup different values, you can use the below

in cell A7 =INDEX(A1:C3,SUM(IF(MAX((B6=A1:C3)*COLUMN(A1:C3)*ROW(A1:C3))=(B6=A1:C3)*COLUMN(A1:C3)*ROW(A1:C3),ROWS(A1:C3)-1,"")),SUM(IF(MAX((B6=A1:C3)*COLUMN(A1:C3)*ROW(A1:C3))=(B6=A1:C3)*COLUMN(A1:C3)*ROW(A1:C3),COLUMN(A1:C3),"")))

it will change if you changed the value in cell A6

Best Regards

 

[linkn00]

Alternatively, if you need an interactive formula to lookup different values, you can use the below

View attachment 20396

in cell A7 =INDEX(A1:C3,SUM(IF(MAX((B6=A1:C3)*COLUMN(A1:C3)*ROW(A1:C3))=(B6=A1:C3)*COLUMN(A1:C3)*ROW(A1:C3),ROWS(A1:C3)-1,"")),SUM(IF(MAX((B6=A1:C3)*COLUMN(A1:C3)*ROW(A1:C3))=(B6=A1:C3)*COLUMN(A1:C3)*ROW(A1:C3),COLUMN(A1:C3),"")))

it will change if you changed the value in cell A6

Best Regards

I like this one. However, when I input c it also returns c. It didn't return a. Is there away for it to return on the above cell?

 

[Peter_SSs]

I am struggling to figure out how to find the last Occurrence in a table

Are you looking in the whole table or just that 3rd column in your example?

If the whole table then you will need to define what you mean by the last occurrence. For example, in the table below if we are looking for the last occurrence of c then the green one is last if we are looking by columns then rows but the blue one is last if looking by rows then columns.

If you are just looking in the third column then I have given a suggestion in F2.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

20 08 15.xlsm
	A	B	C	D	E	F
1	a	b	b		Input	c
2	b	b	c		Result	b
3	a	c	a
4
Above last

Cell Formulas
Range	Formula
F2	F2	=INDEX(C:C,AGGREGATE(14,6,ROW(C1:C3)/(C1:C3=F1),1)-1)

 

[linkn00]

I want to look in the entire table.
What I am using right now is look at each column individually and then use AGGREGATE. This way works however, it is very slow and cause excel to stop working.

Another method that i thought of is using the following:
=INDEX(A:C,MAX((A:C="a")*ROW(A:C)-1),MATCH("a",3:3,0))

However the "3:3" i have to input manually. Is there another way to input the lookup array for the match function using the row number?

 

[Peter_SSs]

look at each column individually

So you are saying that if we were looking at my sample from post 5 then the 'last c' is the one in cell C2 and that is because it is in the right-most column containing a 'c'?

.. and thanks for updating your profile.

 

[Peter_SSs]

So you are saying that if we were looking at my sample from post 5 then the 'last c' is the one in cell C2 and that is because it is in the right-most column containing a 'c'?

If that is the case, then see if this works for you.

20 08 15.xlsm
	A	B	C	D	E	F
1	A	E	I		Input	c
2	B	c	J		Result	F
3	c	F	K
4	D	c	L
5	c	G	M
6	c	H	N
7
Above last

Cell Formulas
Range	Formula
F2	F2	=INDEX(INDEX(A1:C6,0,MAX(IF(A1:C6=F1,SEQUENCE(,COLUMNS(A1:C6)),0))),AGGREGATE(14,6,SEQUENCE(ROWS(A1:C6))/(INDEX(A1:C6,0,MAX(IF(A1:C6=F1,SEQUENCE(,COLUMNS(A1:C6)),0)))=F1),1)-1)

 

[linkn00]

Thank you so much for the fast response. I am looking for right most and bottom most. In this case it should return the value in cell a5 since the last "c" is in cell A6

 

[Peter_SSs]

I am looking for right most and bottom most. In this case it should return the value in cell a5 since the last "c" is in cell A6

So in post 5 the 'last c' is the blue one?