Home 
Search 
Today's Posts 
#1




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




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




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




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




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 
#7




both glad to help
 Don Guillett SalesAid Software "Gary's Student" wrote in message news 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 
Display Modes  


Similar Threads  
Thread  Forum  
Look up data in colum a and find match in colum b  Excel Discussion (Misc queries)  
Look up data in colum a and find match in colum b  Excel Discussion (Misc queries)  
How do i Match all COLUMNS?  Excel Worksheet Functions  
Find a match that;s not exact  Excel Worksheet Functions  
Vlookup, Index & Match  Excel Worksheet Functions 