Match Backwards
 

I need a function like match that gives the last occurrence of a match rather
than the first. For example, if the data in a column is:
1
2
3
4
1
2
3
4
1
2
3
4
then MATCH(2,A1:A12,0) gives 2 (the second row). I need
LASTMATCH(2,A1:A12,0) to give 10 ( the last row containing 2).

Thanks in advance for your help.

--
Gary's Student

Try taking out ,0

--
Don Guillett
SalesAid Software

"Gary's Student" wrote in message
...
I need a function like match that gives the last occurrence of a match
rather
than the first. For example, if the data in a column is:
1
2
3
4
1
2
3
4
1
2
3
4
then MATCH(2,A1:A12,0) gives 2 (the second row). I need
LASTMATCH(2,A1:A12,0) to give 10 ( the last row containing 2).

Thanks in advance for your help.

--
Gary's Student

I tried it. It gave me 6 (the second occurence) rather than 10 (the last
occurence)

We are moving in the right direction, however. Thanks for your fast response.
--
Gary's Student


"Don Guillett" wrote:

Try taking out ,0

--
Don Guillett
SalesAid Software

"Gary's Student" wrote in message
...
I need a function like match that gives the last occurrence of a match
rather
than the first. For example, if the data in a column is:
1
2
3
4
1
2
3
4
1
2
3
4
then MATCH(2,A1:A12,0) gives 2 (the second row). I need
LASTMATCH(2,A1:A12,0) to give 10 ( the last row containing 2).

Thanks in advance for your help.

--
Gary's Student

Try...

=MATCH(2,1/(A1:A12=2))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

OR

=LOOKUP(2,1/(A1:A12=2),ROW(A1:A12)-ROW(A1)+1)

....confirmed with just ENTER.

Note that if you're looking for the last occurrence of 4, change '=2' to
'=4'.

Hope this helps!

In article ,
"Gary's Student" wrote:

I need a function like match that gives the last occurrence of a match rather
than the first. For example, if the data in a column is:
1
2
3
4
1
2
3
4
1
2
3
4
then MATCH(2,A1:A12,0) gives 2 (the second row). I need
LASTMATCH(2,A1:A12,0) to give 10 ( the last row containing 2).

Thanks in advance for your help.

Actually this ARRAY formula will do it . Array formulas must be
entered/edited with ctrl+shift+enter

=MAX(IF(a1:a13=2,ROW(a1:a13)))

--
Don Guillett
SalesAid Software

"Gary's Student" wrote in message
...
I need a function like match that gives the last occurrence of a match
rather
than the first. For example, if the data in a column is:
1
2
3
4
1
2
3
4
1
2
3
4
then MATCH(2,A1:A12,0) gives 2 (the second row). I need
LASTMATCH(2,A1:A12,0) to give 10 ( the last row containing 2).

Thanks in advance for your help.

--
Gary's Student

Thank you both very much. This saves me much time that I used to spend
inverting the array just to find the last occurence!
--
Gary's Student


"Don Guillett" wrote:

Actually this ARRAY formula will do it . Array formulas must be
entered/edited with ctrl+shift+enter

=MAX(IF(a1:a13=2,ROW(a1:a13)))

--
Don Guillett
SalesAid Software

"Gary's Student" wrote in message
...
I need a function like match that gives the last occurrence of a match
rather
than the first. For example, if the data in a column is:
1
2
3
4
1
2
3
4
1
2
3
4
then MATCH(2,A1:A12,0) gives 2 (the second row). I need
LASTMATCH(2,A1:A12,0) to give 10 ( the last row containing 2).

Thanks in advance for your help.

--
Gary's Student

both glad to help

--
Don Guillett
SalesAid Software

"Gary's Student" wrote in message
...
Thank you both very much. This saves me much time that I used to spend
inverting the array just to find the last occurence!
--
Gary's Student


"Don Guillett" wrote:

Actually this ARRAY formula will do it . Array formulas must be
entered/edited with ctrl+shift+enter

=MAX(IF(a1:a13=2,ROW(a1:a13)))

--
Don Guillett
SalesAid Software

"Gary's Student" wrote in
message
...
I need a function like match that gives the last occurrence of a match
rather
than the first. For example, if the data in a column is:
1
2
3
4
1
2
3
4
1
2
3
4
then MATCH(2,A1:A12,0) gives 2 (the second row). I need
LASTMATCH(2,A1:A12,0) to give 10 ( the last row containing 2).

Thanks in advance for your help.

--
Gary's Student