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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Team 2[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]Team 7[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]Team 6[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Team 3[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]Team 8[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Team 4[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]Team 9[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Team 5[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]Team 10[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Team 2[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]Team 4[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Team 8[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]Team 3[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Team 9[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]Team 1[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Team 6[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]Team 7[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Team 10[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]Team 5[/TD]
[/TR]
</tbody>[/TABLE]



Fixtures for Team 1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD]146[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]Team 6[/TD]
[/TR]
[TR]
[TD]147[/TD]
[TD]Team 9[/TD]
[TD][/TD]
[TD]v[/TD]
[TD][/TD]
[TD]Team 1[/TD]
[/TR]
</tbody>[/TABLE]


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).