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

 

[linkn00]

yes

 

[Peter_SSs]

yes

Thanks, that is what I was trying to clarify in post 5.

Anyway, I had the rows/columns the wrong way around by the look of it.
Try this instead.

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

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

 

[linkn00]

=INDEX(A1:C6,MAX(IF(A1:C6=F1,SEQUENCE(ROWS(A1:C6)),0))-1,AGGREGATE(14,6,SEQUENCE(,COLUMNS(A1:C6))/(INDEX(A1:C6,O4,0)=F1),1))

IM sorry but what is this "O4"

 

[linkn00]

I think there is a typo in the formula

 

[Peter_SSs]

IM sorry but what is this "O4"

Sorry, that was part of my working that I missed tidying up.

The corrected (I hope) formula is in F2.
A shorter alternative to consider in F3. It assumed no cells will have text longer than 99 characters. If that is not a safe assumption and text could be longer, a modification could be made. Notice that the two ranges in this formula are both less than the whole table range. One omits the first row (if the last c is there we can't go above it) and one omits the last row (the result can never come from that row). This alternative could also fail if the table &/or texts are very large.

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

Cell Formulas
Range	Formula
F2	F2	=INDEX(A1:C6,MAX(IF(A1:C6=F1,SEQUENCE(ROWS(A1:C6)),0))-1,AGGREGATE(14,6,SEQUENCE(,COLUMNS(A1:C6))/(INDEX(A1:C6,MAX(IF(A1:C6=F1,SEQUENCE(ROWS(A1:C6)),0)),0)=F1),1))
F3	F3	=SUBSTITUTE(RIGHT(SUBSTITUTE(TEXTJOIN(",",1,IF(A2:C6=F1,A1:C5,"")),",",REPT(",",99)),99),",","")

 

[Amit Tandon]

Another option as below:

26Aug19.xlsx
	A	B	C	D	E	F
1	A	c	I
2	B	c	J		c	L A
3	c	F L	K
4	D	c	L A
5	c	G	c
6	E	H	N
Sheet53

Cell Formulas
Range	Formula
F2	F2	=INDEX(A1:C6,AGGREGATE(14,6,ROW(A1:C6)/(A1:C6=E2),1)-1,LOOKUP(2,1/(INDEX(A1:C6,AGGREGATE(14,6,ROW(A1:C6)/(A1:C6=E2),1),)=E2),COLUMN(A1:C6)))

 

[Peter_SSs]

Another option as below:

Though if any rows or columns are subsequently inserted above or left it will return incorrect results (without further modification)

 

[Amit Tandon]

Formulas will mostly get affected by inserting preceding / inbetween rows or columns. Below update, using "ROW(A1:C6)-ROW(A1)+1" instead of "ROW(A1:C6)" and similarly with COLUMN, will resolve most insertions:


=INDEX(A1:C6,AGGREGATE(14,6,(ROW(A1:C6)-ROW(A1)+1)/(A1:C6=E2),1)-1,LOOKUP(2,1/(INDEX(A1:C6,AGGREGATE(14,6,(ROW(A1:C6)-ROW(A1)+1)/(A1:C6=E2),1),)=E2),COLUMN(A1:C6)-COLUMN(A1)+1))

 

[CA_Punit]

Another Option

It will matach even if there are two words Like in B3

Book1
	A	B	C	D	E	F
1	A	c	I
2	B	c	J		F	A
3	c	FL	K
4	D	A	c
5	c	F	n
6	E	H	N
7
Sheet2

Cell Formulas
Range	Formula
F2	F2	=INDEX($A$1:$C$6,INT(AGGREGATE(14,6,ISNUMBER(SEARCH(E2,$A$1:$C$6))*ROW($1:$6)*100+COLUMN($A:$C),1)/100)-1,MOD(AGGREGATE(14,6,ISNUMBER(SEARCH(E2,$A$1:$C$6))*ROW($1:$6)*100+COLUMN($A:$C),1),100))

 

[Peter_SSs]

It will matach even if there are two words Like in B3

Are you sure? If you change cell C6 to "Nf" your formula returns "n". I suspect the OP would still want "A" returned?