Find Value in Table

[Steve 1962]

Hi

I am getting errors in column H. Although the data are shown on this one worksheet, in reality the table (cells A1 to C32) will be located on a separate worksheet.

The objective is to identify whether the team being analysed in cell E2 is playing "HOME" or "AWAY".

Thanks

Book1
	A	B	C	D	E	F	G	H	I
1	Week	Team - Home	Team - Away		Team Analysed	Week	Team	Ground
2	1	Sea Eagles	Rabbitohs		Broncos	1	Roosters	#VALUE!	Should read "HOME"
3	1	Roosters	Broncos			2	Rabbitohs	#VALUE!	Should read "AWAY"
4	1	Knights	Raiders			3	Panthers	#VALUE!	Should read "HOME"
5	1	Warriors	Sharks			4	Cowboys	#VALUE!	Should read "AWAY"
6	1	Storm	Panthers
7	1	Eels	Bulldogs
8	1	Titans	Dragons
9	1	Dolphins	Cowboys
10	2	Broncos	Rabbitohs
11	2	Sharks	Bulldogs
12	2	Panthers	Eels
13	2	Raiders	Tigers
14	2	Cowboys	Knights
15	2	Storm	Warriors
16	2	Sea Eagles	Roosters
17	2	Dolphins	Dragons
18	3	Panthers	Broncos
19	3	Warriors	Raiders
20	3	Roosters	Rabbitohs
21	3	Bulldogs	Titans
22	3	Dragons	Cowboys
23	3	Tigers	Sharks
24	3	Eels	Sea Eagles
25	3	Knights	Storm
26	4	Roosters	Panthers
27	4	Rabbitohs	Bulldogs
28	4	Broncos	Cowboys
29	4	Dragons	Sea Eagles
30	4	Titans	Dolphins
31	4	Warriors	Knights
32	4	Sharks	Raiders
Sheet3

Cell Formulas
Range	Formula
G2:G5	G2	=IFERROR(LOOKUP(2,1/($A$2:$A$32=$F2)/($C$2:$C$32=$E$2),$B$2:$B$32),LOOKUP(2,1/($A$2:$A$32=$F2)/($B$2:$B$32=$E$2),$C$2:$C$32))
H2:H5	H2	=CHOOSE(AGGREGATE(15,6,COLUMN($B$1:$C$1)/($B$2:$C$32=G2)/($A$2:$A$32=F2)-3,1),"Home","Away")

 

[hagia_sofia]

Hello, one of the options could be:

=SUBSTITUTE(MAP(F2:F5,G2:G5,LAMBDA(x,y,XLOOKUP(1,BYCOL(($A$2:$A$32=x)*($B$2:$C$32=y),LAMBDA(a,SUM(a))),$B$1:$C$1))),"Team - ","")

 

[Peter_SSs]

Your column I looks the wrong way round to me. If you are analysing the Broncos, week 1 is at the Roosters ground so Bronco would be "Away" wouldn't they?

Anyway, going with what you have as the desired results

• In col J I have adjusted your formula structure to produce those results. You had -3 where it should be -1. Perhaps the columns have changed and using COLUMN() in your AGGREGATE function like that is risky if the columns might change.
• In col K is another alternative that (like @hagia_sofia's suggestion) produces all the results without having to copy down.
• For you to consider I have also put a shorter alternative in col M to get the list of opponents

24 08 21.xlsm
	A	B	C	D	E	F	G			J	K	L	M
1	Week	Team - Home	Team - Away		Team Analysed	Week	Team			Ground	Ground		Team
2	1	Sea Eagles	Rabbitohs		Broncos	1	Roosters			Home	Home		Roosters
3	1	Roosters	Broncos			2	Rabbitohs			Away	Away		Rabbitohs
4	1	Knights	Raiders			3	Panthers			Home	Home		Panthers
5	1	Warriors	Sharks			4	Cowboys			Away	Away		Cowboys
6	1	Storm	Panthers
7	1	Eels	Bulldogs
8	1	Titans	Dragons
9	1	Dolphins	Cowboys
10	2	Broncos	Rabbitohs
11	2	Sharks	Bulldogs
12	2	Panthers	Eels
13	2	Raiders	Tigers
14	2	Cowboys	Knights
15	2	Storm	Warriors
16	2	Sea Eagles	Roosters
17	2	Dolphins	Dragons
18	3	Panthers	Broncos
19	3	Warriors	Raiders
20	3	Roosters	Rabbitohs
21	3	Bulldogs	Titans
22	3	Dragons	Cowboys
23	3	Tigers	Sharks
24	3	Eels	Sea Eagles
25	3	Knights	Storm
26	4	Roosters	Panthers
27	4	Rabbitohs	Bulldogs
28	4	Broncos	Cowboys
29	4	Dragons	Sea Eagles
30	4	Titans	Dolphins
31	4	Warriors	Knights
32	4	Sharks	Raiders
Steve 1962

Cell Formulas
Range	Formula
G2:G5	G2	=IFERROR(LOOKUP(2,1/($A$2:$A$32=$F2)/($C$2:$C$32=$E$2),$B$2:$B$32),LOOKUP(2,1/($A$2:$A$32=$F2)/($B$2:$B$32=$E$2),$C$2:$C$32))
K2:K5	K2	=TOCOL(IF(B2:B32=E2,"Away",IF(C2:C32=E2,"Home",1/0)),2)
M2:M5	M2	=TOCOL(IF(B2:B32=E2,C2:C32,IF(C2:C32=E2,B2:B32,1/0)),2)
J2:J5	J2	=CHOOSE(AGGREGATE(15,6,COLUMN($B$1:$C$1)/($B$2:$C$32=G2)/($A$2:$A$32=F2)-1,1),"Home","Away")
Dynamic array formulas.

 

[Steve 1962]

Thanks Peter. Yes - you are correct, I had it wrong way round.

Does it matter that the source data (columns A, B & C) are on another worksheet (mentioned in my initial thread) ?

 

[Peter_SSs]

I had it wrong way round.

Does it matter that the source data (columns A, B & C) are on another worksheet

Not at all, though I have modified the formulas a little.

Steve 1962.xlsm
	A	B	C
1	Week	Team - Home	Team - Away
2	1	Sea Eagles	Rabbitohs
3	1	Roosters	Broncos
4	1	Knights	Raiders
5	1	Warriors	Sharks
6	1	Storm	Panthers
7	1	Eels	Bulldogs
8	1	Titans	Dragons
9	1	Dolphins	Cowboys
10	2	Broncos	Rabbitohs
11	2	Sharks	Bulldogs
12	2	Panthers	Eels
13	2	Raiders	Tigers
14	2	Cowboys	Knights
15	2	Storm	Warriors
16	2	Sea Eagles	Roosters
17	2	Dolphins	Dragons
18	3	Panthers	Broncos
19	3	Warriors	Raiders
20	3	Roosters	Rabbitohs
21	3	Bulldogs	Titans
22	3	Dragons	Cowboys
23	3	Tigers	Sharks
24	3	Eels	Sea Eagles
25	3	Knights	Storm
26	4	Roosters	Panthers
27	4	Rabbitohs	Bulldogs
28	4	Broncos	Cowboys
29	4	Dragons	Sea Eagles
30	4	Titans	Dolphins
31	4	Warriors	Knights
32	4	Sharks	Raiders
Sheet1

Steve 1962.xlsm
	A	B	C	D
1	Team Analysed	Week	Team	Ground
2	Broncos	1	Roosters	Away
3		2	Rabbitohs	Home
4		3	Panthers	Away
5		4	Cowboys	Home
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=LET(b,Sheet1!B2:B32,c,Sheet1!C2:C32,TOCOL(IF(b=A2,c,IF(c=A2,b,1/0)),2))
D2:D5	D2	=TOCOL(IF(Sheet1!B2:B32=A2,"Home",IF(Sheet1!C2:C32=A2,"Away",1/0)),2)
Dynamic array formulas.

 

[Steve 1962]

Thanks Peter. I can get it to work on the same worksheet OK but when I refer to the source data on a separate worksheet, I get a #SPILL! error. Obviously I'm doing something wrong. The source data are contained in columns D, E & F on the separate worksheet.

Book1
	D	E	F
1	Week	Team - Home	Team - Away
2	1	Sea Eagles	Rabbitohs
3	1	Roosters	Broncos
4	1	Knights	Raiders
5	1	Warriors	Sharks
6	1	Storm	Panthers
7	1	Eels	Bulldogs
8	1	Titans	Dragons
9	1	Dolphins	Cowboys
10	2	Broncos	Rabbitohs
11	2	Sharks	Bulldogs
12	2	Panthers	Eels
13	2	Raiders	Tigers
14	2	Cowboys	Knights
15	2	Storm	Warriors
16	2	Sea Eagles	Roosters
17	2	Dolphins	Dragons
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1,D1:I1	Cell Value	=""	text	YES
A1,D1:I1	Cell Value	=$B$1	text	NO
E2:F17	Cell Value	=""	text	YES
E2:F17	Cell Value	=$B$1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E2:F17	List	=Teams

 

[Peter_SSs]

I get a #SPILL! error.

The formula only needs to go in the top cell. Select all the cells below the top one in each column and 'Delete'

Steve 1962.xlsm
	D	E	F
1	Week	Team - Home	Team - Away
2	1	Sea Eagles	Rabbitohs
3	1	Roosters	Broncos
4	1	Knights	Raiders
5	1	Warriors	Sharks
6	1	Storm	Panthers
7	1	Eels	Bulldogs
8	1	Titans	Dragons
9	1	Dolphins	Cowboys
10	2	Broncos	Rabbitohs
11	2	Sharks	Bulldogs
12	2	Panthers	Eels
13	2	Raiders	Tigers
14	2	Cowboys	Knights
15	2	Storm	Warriors
16	2	Sea Eagles	Roosters
17	2	Dolphins	Dragons
18	3	Panthers	Broncos
19	3	Warriors	Raiders
20	3	Roosters	Rabbitohs
21	3	Bulldogs	Titans
22	3	Dragons	Cowboys
23	3	Tigers	Sharks
24	3	Eels	Sea Eagles
25	3	Knights	Storm
26	4	Roosters	Panthers
27	4	Rabbitohs	Bulldogs
28	4	Broncos	Cowboys
29	4	Dragons	Sea Eagles
30	4	Titans	Dolphins
31	4	Warriors	Knights
32	4	Sharks	Raiders
Sheet1

Steve 1962.xlsm
	A	B	C	D
1	Team Analysed	Week	Team	Ground
2	Broncos	1	Roosters	Away
3		2	Rabbitohs	Home
4		3	Panthers	Away
5		4	Cowboys	Home
6
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=LET(b,Sheet1!E2:E32,c,Sheet1!F2:F32,TOCOL(IF(b=A2,c,IF(c=A2,b,1/0)),2))
D2:D5	D2	=TOCOL(IF(Sheet1!E2:E32=A2,"Home",IF(Sheet1!F2:F32=A2,"Away",1/0)),2)
Dynamic array formulas.

 

[Steve 1962]

Thanks Peter. I now do not receive the SPILL error so that is fixed. Only thing now is that the range of formulas in column E, only extends down as far as row 25. It does not complete down all the way to row 28. I have pressed CONTROL + arrow down to the last row 1048576 in column E and there are no entries below row 25. Not sure why it doesn't complete to row 28.

Book1
	A	B	C	D	E	F
1	Broncos		Week	Team	Ground
2			1	Roosters	Away
3			2	Rabbitohs	Home
4			3	Panthers	Away
5			4	Cowboys	Home
6			5	Storm	Away
7			6	Dolphins	Home
8			7	Raiders	Home
9			8	Tigers	Away
10			9	Roosters	Home
11			10	Eels	Away
12			11	Sea Eagles	Away
13			12	Titans	Home
14			13	#N/A	Home
15			14	Sharks	Away
16			15	Rabbitohs	Away
17			16	#N/A	Home
18			17	Warriors	Home
19			18	Panthers	Away
20			19	Dragons	Home
21			20	Knights	Away
22			21	Bulldogs	Away
23			22	Titans	Home
24			23	Cowboys	Away
25			24	#N/A	Home	Range end at this row
26			25	Eels
27			26	Dolphins
28			27	Storm
Broncos

Cell Formulas
Range	Formula
A1	A1	=TEXTAFTER(CELL("filename",A1),"]")
E2	E2	=TOCOL(IF(Table!E2:E217=A1,"Home",IF(Table!F2:F217=A1,"Away",1/0)),2)
D2:D28	D2	=IFERROR(LOOKUP(2,1/(Main[Week]=$C2)/(Main[Team - Away]=$A$1),Main[Team - Home]),LOOKUP(2,1/(Main[Week]=$C2)/(Main[Team - Home]=$A$1),Main[Team - Away]))

 

[Peter_SSs]

Can you show us the 'Table' sheet with XL2BB as well?

Is 'Table' the sheet with the 'Main' table on it?

 

[Steve 1962]

OK Peter. Here is the "Table' sheet, containing the 'Main' table. I have only provided up to row 25, as the entire table is quite long (covers 27 x weeks over a couple of hundred rows).

Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1			Sequence	Week	Team - Home	Team - Away	Score - Home	Score - Away	Tries - Home	Tries - Away	Goals - Home	Goals - Away	P / D	Column1	Result - Home	Result - Away	Draw	Winner	Win	Selection
2			1	1	Sea Eagles	Rabbitohs	36	24	6	5	6.0	2.0	12	Home	W			Sea Eagles	1	Sea Eagles
3			2	1	Roosters	Broncos	20	10	3	2	4.0	1.0	10	Home	W			Roosters	1	Roosters
4			3	1	Knights	Raiders	12	28	2	5	2.0	4.0	-16	Away		W		Raiders	1	Raiders
5			4	1	Warriors	Sharks	12	16	2	3	2.0	2.0	-4	Away		W		Sharks	0	Warriors
6			5	1	Storm	Panthers	8	0	1	0	2.0	0.0	8	Home	W			Storm	1	Storm
7			6	1	Eels	Bulldogs	26	8	4	2	5.0	0.0	18	Home	W			Eels	1	Eels
8			7	1	Titans	Dragons	4	28	1	5	0.0	4.0	-24	Away		W		Dragons	0	Titans
9			8	1	Dolphins	Cowboys	18	43	3	7	3.0	7.5	-25	Away		W		Cowboys	0	Dolphins
10			9	2	Broncos	Rabbitohs	28	18	5	3	4.0	3.0	10	Home	W			Broncos	1	Broncos
11			10	2	Sharks	Bulldogs	25	6	4	1	4.5	1.0	19	Home	W			Sharks	1	Sharks
12			11	2	Panthers	Eels	26	18	5	3	3.0	3.0	8	Home	W			Panthers	0	Eels
13			12	2	Raiders	Tigers	32	12	5	2	6.0	2.0	20	Home	W			Raiders	1	Raiders
14			13	2	Cowboys	Knights	21	20	4	3	2.5	4.0	1	Home	W			Cowboys	1	Cowboys
15			14	2	Storm	Warriors	30	26	5	4	5.0	5.0	4	Home	W			Storm	1	Storm
16			15	2	Sea Eagles	Roosters	21	14	3	3	4.5	1.0	7	Home	W			Sea Eagles	1	Sea Eagles
17			16	2	Dolphins	Dragons	38	0	7	0	5.0		38	Home	W			Dolphins	0	Dragons
18			17	3	Panthers	Broncos	34	12	6	2	5.0	2.0	22	Home	W			Panthers	1	Panthers
19			18	3	Warriors	Raiders	18	10	3	2	3.0	1.0	8	Home	W			Warriors	0	Raiders
20			19	3	Roosters	Rabbitohs	48	6	8	1	8.0	1.0	42	Home	W			Roosters	1	Roosters
21			20	3	Bulldogs	Titans	32	0	6	0	4.0		32	Home	W			Bulldogs	0	Titans
22			21	3	Dragons	Cowboys	24	46	4	8	4.0	7.0	-22	Away		W		Cowboys	1	Cowboys
23			22	3	Tigers	Sharks	32	6	5	1	6.0	1.0	26	Home	W			Tigers	0	Sharks
24			23	3	Eels	Sea Eagles	28	24	5	4	4.0	4.0	4	Home	W			Eels	0	Sea Eagles
25			24	3	Knights	Storm	14	12	2	2	3.0	2.0	2	Home	W			Knights	0	Storm
Sheet5

Cell Formulas
Range	Formula
M2:M25	M2	=IF(AND(Main[@[Score - Home]]="",Main[@[Score - Away]]=""),"",G2-H2)
N2:N25	N2	=IF(AND(Main[@[Score - Home]]="",Main[@[Score - Away]]=""),"",IF(Main[@[Score - Home]]>Main[@[Score - Away]],"Home",IF(Main[@[Score - Home]]<Main[@[Score - Away]],"Away","Draw")))
O2:O25	O2	=IF(Main[@[Score - Home]]>Main[@[Score - Away]],"W","")
P2:P25	P2	=IF(Main[@[Score - Home]]<Main[@[Score - Away]],"W","")
Q2:Q25	Q2	=IF(Main[@[Score - Home]]+Main[@[Score - Away]]=0,"",IF(Main[@[Score - Home]]=Main[@[Score - Away]],"D",""))
R2:R25	R2	=IF(Main[@[Score - Home]]>Main[@[Score - Away]],Main[@[Team - Home]],IF(Main[@[Score - Home]]=Main[@[Score - Away]],"",Main[@[Team - Away]]))
S2:S25	S2	=IF(AND(Main[@[Score - Home]]=0,Main[@[Score - Away]]=0),"",IF(Main[@Winner]=Main[@Selection],1,0))

Cells with Data Validation
Cell	Allow	Criteria
E2:F25	List	=Teams