n-th match in array without array: index(,match+n-1,)?

regresss · Oct 14, 2017

Hi, I have read online that one can use array formula to return nth match in array using index match. There is also some other, rather convoluted non-array formula solution.

I wonder, why one cannot not increment match value by n-1? That is assuming I have sorted my table and matches are on consecutive rows (in consecutive columns)?

A1:
Bob
John
John
Bob

B1:
1
2
3
4

Why does it not work to return the 2nd John value with INDEX(1:4,MATCH("John",A:A,0)+1,2)? Would you know of any alternative?

Edit: found out it actually works

. Great!

regresss · Oct 14, 2017

In A1 above, the last value cannot be Bob as I have sorted the table by this column ^^

FDibbins · Oct 14, 2017

INDEX is based in RANGE, row, column, so the problem if not with INDEX

MATCH will always only return the 1st match it finds - then stop looking.
The way you have MATCH structured above, it will find the row John is in, then go down 1 more row. So yes, you can do the n+1 thing to get consecutive rows, but if, for instance, you have 3 Johns and then a Mike, and use +4, you will get Mike, not John.

2 ways I can see to get around this

1, use a helper column to sequence-count "john"
=countif($A$1:a1,A1) copied down will give each name a "count" value.
You could then combine that with the name to get a unique ID
=A1&" "&countif($A$1:a1,A1)
To find the n-th John, you would use =MATCH("John "&"n,helper-column,

to find what you need

Another way might be to do a sequential count for John, and use that to add it to the MATCH

regresss · Oct 14, 2017

FDibbins said:
Another way might be to do a sequential count for John, and use that to add it to the MATCH

Could you elaborate on this? Helper columns are a big no no.

I also wonder if i could find the last observation of John in some simple way, assuming values cannot be sorted.

FDibbins · Oct 14, 2017

I don't see why helpers are a "big no"?? They can simplify things, an can be hidden

This would use a helper, it works on unsorted data...
[Table="width:, class:grid"][tr][td] [/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][td]

D

[/td][td]

E

[/td][td]

F

[/td][/tr]
[tr][td]

1

[/td][td]Name[/td][td]value[/td][td]Helper[/td][td][/td][td]Name[/td][td]value[/td][/tr]

[tr][td]

2

[/td][td]aa[/td][td]

10

[/td][td]aa 1[/td][td][/td][td]bb[/td][td]

50

[/td][/tr]

[tr][td]

3

[/td][td]bb[/td][td]

20

[/td][td]bb 1[/td][td][/td][td]

2

[/td][td][/td][/tr]

[tr][td]

4

[/td][td]cc[/td][td]

30

[/td][td]cc 1[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5

[/td][td]aa[/td][td]

40

[/td][td]aa 2[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6

[/td][td]bb[/td][td]

50

[/td][td]bb 2[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7

[/td][td]cc[/td][td]

60

[/td][td]cc 2[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8

[/td][td]aa[/td][td]

70

[/td][td]aa 3[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9

[/td][td]bb[/td][td]

80

[/td][td]bb 3[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10

[/td][td]cc[/td][td]

90

[/td][td]cc 3[/td][td][/td][td][/td][td][/td][/tr]
[/table]
C2=A2&" "&COUNTIF($A$2:A2,A2)
copied down
F2=INDEX(B:B,MATCH(E2&" "&E3,C:C,0))

For SORTED data, this should work (which you pretty much already had)...
[Table="width:, class:grid"][tr][td] [/td][td]

H

[/td][td]

I

[/td][td]

J

[/td][td]

K

[/td][td]

L

[/td][/tr]
[tr][td]

1

[/td][td]Name[/td][td]value[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2

[/td][td]aa[/td][td]

10

[/td][td][/td][td]Name[/td][td]value[/td][/tr]

[tr][td]

3

[/td][td]aa[/td][td]

20

[/td][td][/td][td]bb[/td][td]

50

[/td][/tr]

[tr][td]

4

[/td][td]aa[/td][td]

30

[/td][td][/td][td]

2

[/td][td][/td][/tr]

[tr][td]

5

[/td][td]bb[/td][td]

40