MATCH to find last occurence

[excelos]

Hello

Can you explain please how does =MATCH(2,1/(A1:A10="a")) enables to find last occurence of "a" ?

I mean the lookup array can be something like: {#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1}.

Then, MATCH should find the "first occurence that is <2", which is position 2. Why though it finds the "last occurence that is <2"?

Also, do you know any alternative methods to search for first/last occurence and search forwards/backwards? I know XLOOKUP has such functionality but it may not be available in the local machine.

Thanks!

 

[SanjayGMusafir]

Hello

Can you explain please how does =MATCH(2,1/(A1:A10="a")) enables to find last occurence of "a" ?

I mean the lookup array can be something like: {#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1}.

Then, MATCH should find the "first occurence that is <2", which is position 2. Why though it finds the "last occurence that is <2"?

Also, do you know any alternative methods to search for first/last occurence and search forwards/backwards? I know XLOOKUP has such functionality but it may not be available in the local machine.

Thanks!

When we use =MATCH(2,1/(A1:A10="a"))

1/(A1:A10="a") shall return 1 for every match. But Function is searching for 2 as match so would return the last/maximum value.

You can also use

=Lookup(2,1/(A1:A10="a"),A1:A10)

Hope it helps.

 

[Peter_SSs]

do you know any alternative methods to search for first/last occurence and search forwards/backwards?

23 09 09.xlsm
	A	B	C
1	b	First	2
2	a	Last	8
3	c
4	b
5	c
6	a
7	c
8	a
9	d
10	s
Find last

Cell Formulas
Range	Formula
C1	C1	=AGGREGATE(15,6,(ROW(A1:A10)-ROW(A1)+1)/(A1:A10="a"),1)
C2	C2	=AGGREGATE(14,6,(ROW(A1:A10)-ROW(A1)+1)/(A1:A10="a"),1)