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

 

[Peter_SSs]

Seems we cannot see the 'Main' table nor all the data on this sheet so basically impossible to see why it is not extending down as far as you expect. Instead, can you upload the file to dropBox or OneDrive or Google Drive etc and provide a publicly shared link here?

 

[Steve 1962]

OK Peter. Will do. Just have to work out how to share via OneDrive or Google. Never had to do it before.

 

[Steve 1962]

Hi

Below is my attempt to share the Excel file.

Football Results.xlsm​

1drv.ms

The issue is with column E on the BRONCOS worksheet where the TOCOL function only extends down to row 25.

Thanks

 

[Peter_SSs]

The issue is that the Table sheet has 27 rounds but the Broncos only play in 24 of them (due to byes). The subject of byes did not occur or get a mention with your original sample/question.
I think a different approach may be needed. I'll have a think about it.

 

[Steve 1962]

Thanks Peter.

 

[Peter_SSs]

What about these two formulas?

Steve 1962 Football Results.xlsm
	A	B	C	D	E
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	Bye	Bye
15			14	Sharks	Home
16			15	Rabbitohs	Away
17			16	Bye	Bye
18			17	Warriors	Away
19			18	Panthers	Home
20			19	Dragons	Home
21			20	Knights	Away
22			21	Bulldogs	Home
23			22	Titans	Away
24			23	Cowboys	Away
25			24	Bye	Bye
26			25	Eels	Home
27			26	Dolphins	Away
28			27	Storm	Home
Broncos

Cell Formulas
Range	Formula
A1	A1	=TEXTAFTER(CELL("filename",A1),"]")
D2:D28	D2	=BYROW(C2:C28,LAMBDA(r,IFERROR(SUBSTITUTE(CONCAT(FILTER(Main[[Team - Home]:[Team - Away]],(Main[Week]=r)*((Main[Team - Home]=A$1)+(Main[Team - Away]=A$1)))),A$1,""),"Bye")))
E2:E28	E2	=IF(D2#="Bye",D2#,IF(COUNTIFS(Main[Week],C2:C28,Main[Team - Home],A1),"Home","Away"))
Dynamic array formulas.

 

[Steve 1962]

Peter - I have no idea how you do it but this is absolutely BRILLIANT !!!

Works as required to do so.

Thanks for your patience and great knowledge with this.

Steve

 

[Peter_SSs]

You're welcome. Glad to help.