Remember Me?

#1
May 21st 05, 03:33 PM
 Gary's Student Posts: n/a
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).

--
Gary's Student

#2
May 21st 05, 03:41 PM
 Don Guillett Posts: n/a

#3
May 21st 05, 04:08 PM
 Gary's Student Posts: n/a

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).

--
Gary's Student

#4
May 21st 05, 04:10 PM
 Domenic Posts: n/a

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).

#5
May 21st 05, 04:18 PM
 Don Guillett Posts: n/a

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).

--
Gary's Student

#6
May 21st 05, 05:01 PM
 Gary's Student Posts: n/a

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).

--
Gary's Student

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Chris(new user) Excel Discussion (Misc queries) 1 March 22nd 05 01:41 PM Chris(new user) Excel Discussion (Misc queries) 1 March 19th 05 09:27 PM Siddiqui Excel Worksheet Functions 1 November 9th 04 08:26 PM Phyllis Excel Worksheet Functions 0 November 8th 04 08:12 PM Phyllis Excel Worksheet Functions 1 November 8th 04 06:11 PM

All times are GMT +1. The time now is 03:49 AM.