Find the last occurrence

[30percent]

Hi,

I have the following formula:

=INDEX('Paste'!A:A,MATCH(TRIM(B36),'Paste'!A:A,0)+1)

What's the easiest way to tweak it so that instead of finding the first occurrence and returning its index, it would find the LAST OCCURRENCE?

thanks!

 

[Asbestos_Jen]

This link gives three different strategies: Find the Last Occurrence of a Lookup Value a List in Excel

 

[Peter_SSs]

Try this. Where I have 2000 use a number that will surely be larger than the number of rows of data in 'Paste' but I would avoid whole column references for those ranges.

=INDEX(Paste!A:A,AGGREGATE(14,6,ROW(Paste!A1:A2000)/(Paste!A1:A2000=TRIM(B36)),1)+1)

 

[Scott Huish]

If you are just trying to return the value below the last occurrence from the Paste sheet

=LOOKUP(99^99,IF(Paste!A1:A2000=TRIM(B36),Paste!A2:A2001))

You may have to confirm this with CTRL-SHIFT-ENTER

 

[Peter_SSs]

If you are just trying to return the value below the last occurrence from the Paste sheet

=LOOKUP(99^99,IF(Paste!A1:A2000=TRIM(B36),Paste!A2:A2001))

You may have to confirm this with CTRL-SHIFT-ENTER

We don't know what format the OP's data is, but wouldn't that only work if all the values in Paste immediately below whatever the B36 value is are numerical?
For example, it returns 55 here instead of "www"

24 03 27.xlsm
	A
1
2	b
3	a
4	55
5
6
7
8
9	a
10	www
11	z
12	22
13
Paste

24 03 27.xlsm
	B	C
36	a	55
30percent

Cell Formulas
Range	Formula
C36	C36	=LOOKUP(99^99,IF(Paste!A1:A2000=TRIM(B36),Paste!A2:A2001))