| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
get 2nd occurence value
Match function fetches row number of 1st occurence of a value in a range,How
to get 2nd or 3rd or later occurences of that value in a range? |
|
#2
|
|||
|
|||
|
Assuming that A1:A100 contains your data, and that B1 contains your
lookup value, try the following for the second occurrence... =SMALL(IF(A1:A100=B1,ROW(A1:A100)-ROW(A1)+1),2) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. For the third occurrence, change the 2 at the end of the formula to 3. Hope this helps! In article , TUNGANA KURMA RAJU wrote: Match function fetches row number of 1st occurence of a value in a range,How to get 2nd or 3rd or later occurences of that value in a range? |
|
#3
|
|||
|
|||
|
Hello DOMENIC,
Thanks.you have done it.It helped me to solve a problem.With regards "Domenic" wrote: Assuming that A1:A100 contains your data, and that B1 contains your lookup value, try the following for the second occurrence... =SMALL(IF(A1:A100=B1,ROW(A1:A100)-ROW(A1)+1),2) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. For the third occurrence, change the 2 at the end of the formula to 3. Hope this helps! In article , TUNGANA KURMA RAJU wrote: Match function fetches row number of 1st occurence of a value in a range,How to get 2nd or 3rd or later occurences of that value in a range? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Triple occurence formula | Excel Worksheet Functions | |||
| Identify occurence in "array" which meets condition(s) | Excel Discussion (Misc queries) | |||
| how to count occurence of numbers separated by , in a single cell | Excel Worksheet Functions | |||
| Using VLOOKUP,what if I have more than one occurence of my Lookup. | Excel Worksheet Functions | |||
| I need to count the occurence of text in a range of cells (which . | Excel Worksheet Functions | |||
Linear Mode
