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