Index match where one of the elements is ideally a variable

[Red over White]

I am trying to create an INDEX/MATCH formula where one of the criteria will eventually be a variable. To reach this point I have been using codes that normally signify the look up value is anything but a blank cell, but my attempts to date (cells G25-G27) have not worked. The extract below is part of a larger file where Column C has a number of different league names, and so whilst the formula at G28 works and selects the correct date it is only because I have linked the league name to a cell (D23) which has that name.

How can I express look up value as something other than blank?

Rugby frequency.xlsx
	A	B	C	D	E	F	G	H
1	Season	Date	League	Team	H/A	For	Against	Result
2	2021-22	04/09/21	North Prem	York	H	33	10	W
3	2021-22	11/09/21	North Prem	Preston Grasshoppers	H	29	5	W
4	2021-22	18/09/21	North Prem	Macclesfield	A	58	24	W
5	2021-22	25/09/21	North Prem	Sandal	H	10	17	L
6	2021-22	02/10/21		Billingham	A	16	16	D
7	2021-22	16/10/21	North Prem	Blackburn	H	33	19	W
8	2021-22	23/10/21	North Prem	Lymm	A	30	15	W
9	2021-22	30/10/21	North Prem	Kirkby Lonsdale	H	74	5	W
10	2021-22	06/11/21		Macclesfield	H	58	13	W
11	2021-22	13/11/21	North Prem	Rossendale	A	43	16	W
12	2021-22	20/11/21	North Prem	Wirral	H	39	14	W
13	2021-22	27/11/21	North Prem	Alnwick	P
14	2021-22	04/12/21	North Prem	Burnage	H	50	17	W
15	2021-22	11/12/21	North Prem	Northwich	A	59	0	W
16
17
18
19
20
21
22				Macclesfield
23				North Prem	Highest score		58
24				Non league match		When	06/11/21	""
25				League match			#N/A	"<>"""
26							#N/A	"<>0"
27							#N/A	">0"
28							18/09/21
Data

Cell Formulas
Range	Formula
H2:H15	H2	=IF(F2="","",IF(F2>G2,"W",IF(F2<G2,"L","D")))
G23	G23	=INDEX(B2:F15,MATCH(D22,D2:D15,0),5)
G24	G24	=INDEX(B2:B15,MATCH(1,(""=C2:C15)*(D22=D2:D15)*(G23=F2:F15),0))
G25	G25	=INDEX(B$2:B$15,MATCH(1,("<>"""=C$2:C$15)*(D$22=D$2:D$15)*(G$23=F$2:F$15),0))
G26	G26	=INDEX(B$2:B$15,MATCH(1,("<>0"=C$2:C$15)*(D$22=D$2:D$15)*(G$23=F$2:F$15),0))
G27	G27	=INDEX(B$2:B$15,MATCH(1,(">0"=C$2:C$15)*(D$22=D$2:D$15)*(G$23=F$2:F$15),0))
G28	G28	=INDEX(B$2:B$15,MATCH(1,(D23=C$2:C$15)*(D$22=D$2:D$15)*(G$23=F$2:F$15),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jtakw]

Hi,

Change your G25 formula to:

=INDEX(B$2:B$15,MATCH(1,(""<>C$2:C$15)*(D$22=D$2:D$15)*(G$23=F$2:F$15),0))

Array Entered CSE

 

[Fluff]

Not entirely sure what you are trying to do, but maybe

=INDEX(B$2:B$15,MATCH(1,(C$2:C$15<>"")*(D$22=D$2:D$15)*(G$23=F$2:F$15),0))

 

[Red over White]

Thanks jtakw, that works

 

[jtakw]

You're welcome, thanks for the feedback.