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

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

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




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

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.

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

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





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

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Look up data in colum a and find match in colum b Chris(new user) Excel Discussion (Misc queries) 1 March 22nd 05 01:41 PM
Look up data in colum a and find match in colum b Chris(new user) Excel Discussion (Misc queries) 1 March 19th 05 09:27 PM
How do i Match all COLUMNS? Siddiqui Excel Worksheet Functions 1 November 9th 04 08:26 PM
Find a match that;s not exact Phyllis Excel Worksheet Functions 0 November 8th 04 08:12 PM
Vlookup, Index & Match Phyllis Excel Worksheet Functions 1 November 8th 04 06:11 PM


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

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017