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

[regresss]

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]

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

 

[FDibbins]

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]

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]

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​

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

[tr][td]

6​

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

50​

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

[tr][td]

7​

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

60​

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

[tr][td]

8​

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

70​

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

[tr][td]

9​

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

80​

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

[tr][td]

10​

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

90​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]
L3=INDEX(I:I,MATCH(K3,H:H,0)+K4-1)

I don't know of any other (simple) non-array way to do this

 

[Peter_SSs]

Here are 2 ways to extract the nth match directly.

Book1
	A	B	C	D	E	F	G	H
1	Name	value			Name	Match	value	value
2	aa	10			bb	2	50	50
3	bb	20			aa	3	70	70
4	cc	30			cc	1	30	30
5	aa	40
6	bb	50
7	cc	60
8	aa	70
9	bb	80
10	cc	90
nth match

Cell Formulas
Range	Formula
G2	=INDEX(B$2:B$10,AGGREGATE(15,6,(ROW(B$2:B$10)-ROW(B$2)+1)/(A$2:A$10=E2),F2))
H2	{=INDEX(B$2:B$10,SMALL(IF(A$2:A$10=E2,ROW(B$2:B$10)-ROW(B$2)+1),F2))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[FDibbins]

Pete, I considered using (at least) INDEX/SMALL/IF, but OP seemed to indicate they wanted a non-array method

 

[Peter_SSs]

Pete, I considered using (at least) INDEX/SMALL/IF, but OP seemed to indicate they wanted a non-array method

Just offering options. (OP also indicated no helper columns )