Array - Index - iferror formula

[ronie85]

I have a list of football results
[TABLE="width: 218"]
<tbody>[TR]
[TD]Stoke (cell K8)
[/TD]
[TD]0 (cell L8)
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Aston Villa (cell K9)
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]West Brom (cell K10)
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Sunderland (cell K11)
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Stoke (cell K12)
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Tottenham (cell K13)
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]Arsenal (cell K14)
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Stoke (cell K15)
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Man Utd (cell K16)
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Swansea (cell K17)
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Leicester (cell K18)
[/TD]
[TD]2 (cell L18)
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

The first entrance of 'Stoke' is in cell K8 and the it will reappear 37 more times between cells K8:K767. So I need these 38 "Stoke" to appear in the order they are in Column K, Starting with cell W8 and running to cell W45. I also need the next 2 cells to the right to contain the data too. So W8=Stoke X8=0 Y8=1. Then the next row starting at W9 will say Stoke, but X9 & Y9 will have two different numbers depending on the next time stoke appears between K8:K767. Note that the data that will be in X8 comes from L8, and the data from Y8 comes from M8. I hope this makes sense as its a lot to take in.
I have been told to use formula =IFERROR(INDEX(A$1:A$20,SMALL(IF($A$1:$A$20="Stoke",ROW($A$1:$A$20)),ROWS($A$1:A1))),"") but it doesn’t seem to work. Can someone help me please?

 

[Aladin Akyurek]

This formula

=IFERROR(SMALL(IF($V$8:$V$767=$AB$6,ROW($V$8:$V$767)-ROW($V$8)+1),ROWS($AE$8:AE8)),"")

looks at V8.

a. What is the formula that you have in V8?
b. What is the value that V8 currently displays?

 

[ronie85]

The formula in V8 is =J8
The formula in J8 is =INDEX(IF(MOD(ROWS(J$1:J7), 2),F:F,I:I ), INT((ROWS(J$1:J7)-1)/2) + 1)
The value V8 currently displays is ‘Stoke’

 

[Aladin Akyurek]

The formula in V8 is =J8
The formula in J8 is =INDEX(IF(MOD(ROWS(J$1:J7), 2),F:F,I:I ), INT((ROWS(J$1:J7)-1)/2) + 1)
The value V8 currently displays is ‘Stoke’

What is the result of

=LEN(J8)

 

[ronie85]

[TABLE="width: 448"]
<tbody>[TR]
[TD]Search
[/TD]
[TD]Arsenal
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Result
[/TD]
[TD] 2
[/TD]
[TD]1
[/TD]
[TD] 2
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD] 2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2
[/TD]
[TD] 2
[/TD]
[TD] 2
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD] 1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1
[/TD]
[TD] 1
[/TD]
[TD]#NUM!
[/TD]
[TD]#NUM!
[/TD]
[TD]#NUM!
[/TD]
[TD]#NUM!
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 2
[/TD]
[TD]2
[/TD]
[TD]#NUM!
[/TD]
[TD]#NUM!
[/TD]
[TD]#NUM!
[/TD]
[TD]#NUM!
[/TD]
[/TR]
</tbody>[/TABLE]

I’ve worked it out. Search = cell S1
T2 formula is =INDEX($K$8:$K$767, SMALL(IF(ISNUMBER(SEARCH($T$1, $J$8:$J$767)), MATCH(ROW($J$8:$J$767), ROW($J$8:$J$767))), ROW(J1)))
U2 Formula is =INDEX($L$8:$L$767, SMALL(IF(ISNUMBER(SEARCH($T$1, $J$8:$J$767)), MATCH(ROW($J$8:$J$767), ROW($J$8:$J$767))), ROW(J1)))
Colums J, K & L are;
[TABLE="width: 208"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Overall
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Overall
[/TD]
[TD]For
[/TD]
[TD]Against
[/TD]
[/TR]
[TR]
[TD]Stoke
[/TD]
[TD] 0
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Aston Villa
[/TD]
[TD]1
[/TD]
[TD] 0
[/TD]
[/TR]
[TR]
[TD]West Brom
[/TD]
[TD] 2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Sunderland
[/TD]
[TD]2
[/TD]
[TD] 2
[/TD]
[/TR]
[TR]
[TD]West Ham
[/TD]
[TD] 0
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Tottenham
[/TD]
[TD]1
[/TD]
[TD] 0
[/TD]
[/TR]
[TR]
[TD]Arsenal
[/TD]
[TD] 2
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

I don't see why you don't answer the question...

Try to add a few rows before the row of T2. Does the formula you have in T2 still work correctly?

 

[ronie85]

I don't need any rows before t2. And yeah it works correctly.

 

[Aladin Akyurek]

I don't need any rows before t2. And yeah it works correctly.

Great, I see why... I'd say try the same in front of T1, but never mind.

 

[ronie85]

The reason I started in T2 is because the 'Arsenal' is in T1. The formula is searching for 'Arsenal' within a string of data and it keeps my sheet neat with the team names in row 1. I have 19 other teams within this sheet.