Returning two values from a table array based on a value

[Phil-T]

I'm trying to return 2 values from a table selection, based on a value i.e. if I look up the table for "Team 1" - i'd like to return Team 1 v Team 6, Team 9 v Team 1, and so on, to a Fixtures Section in a different area of my Excel Sheet.

However, the look up value "Team 1" could be in column J or N. I've tried Index, Match, VLookup, IF statements etc....

Any ideas would be greatly appreciated

See below:

	J	K	L	M	N
8	Team 2		v		Team 7
9	Team 1		v		Team 6
10	Team 3		v		Team 8
11	Team 4		v		Team 9
12	Team 5		v		Team 10
13
14	Team 2		v		Team 4
15	Team 8		v		Team 3
16	Team 9		v		Team 1
17	Team 6		v		Team 7
18	Team 10		v		Team 5

<tbody>
</tbody>

Fixtures for Team 1

	J	K	L	M	N
146	Team 1		v		Team 6
147	Team 9		v		Team 1

<tbody>
</tbody>

Many thanks.....

 

[Aladin Akyurek]

O146, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF($J$8:$N$18="Team 1",ROW($J$8:$N$18)-ROW($J$8)+1),ROWS(O$146:O146)),"")

J146, copied down:

=IF($O146="","",INDEX($J$8:$J$18,$O146))

L146, copied down:

=IF($O146="","","v")

N146, copied down:

=IF($O146="","",INDEX($N$8:$N$18,$O146))

 

[Phil-T]

Aladin - that's absolutely fantastic and i've it working now in my excel sheet.

Brilliant!!!

Many thanks

 

[Jon von der Heyden]

Hi Phil

In future please stick to your original thread. You can bump it after a day if you haven't had satisfactory replies (just reply to your own thread to bump it).