Index / Match with multiple criter not working.

[JeffGrant]

Hi All,

Sorry I had to paste this as a table, for some reason the XL2bb addin is not working on my computer under a newly installed windows 11.

But anyway...
Legend: ES to EZ are columns headers, 41 to 52 in column ET are row numbers from the original sheet

I have three ways below to use a multi criteria look in an array and for seom reason the Index/Match wont work.

in ES42, there is =MATCH(ER42,EU41:EZ41,0) which returns column 3 - Ok
in ES43, there is =MATCH(ER43,EU42:EU54,0) which returns row 8. - Ok
in ER44, there is =INDEX(EU42:EZ54,ES43,ES42), which returns 18%. This is using the Match Results cell location instead of the matched results - Ok

in ER48, there is =XLOOKUP(ER42,EV41:EZ41,XLOOKUP(ER43,EU42:EU58,EV42:EZ58),"Error",0), which returns 18% - Ok

Now, in ER46, there is =INDEX(EV42:EZ58,MATCH(1,(ER43=EU42:EU58)*(ER42=EU41:EZ41),0)), which is using the multi criteria Match syntax and it does not work.

I am little perplexed, any ideas & thanks for helping me understand.

		Column
	ER	ES	ET	EU	EV	EW	EX	EY	EZ
v16	55%		41		1​	2​	3​	4​	5​
O2 value	2	3​	42	4​	25%​	0%​	0%​	0%​	0%​
M2 Value	11	8​	43	5​	20%​	0%​	0%​	0%​	0%​
results	18%		44	6​	17%​	0%​	0%​	0%​	0%​
			45	7​	14%​	0%​	0%​	0%​	0%​
index/match	#N/A		46	8​	0%​	25%​	0%​	0%​	0%​
			47	9​	0%​	22%​	0%​	0%​	0%​
double xlookup	18%		48	10​	0%​	20%​	0%​	0%​	0%​
			49	11​	0%​	18%​	0%​	0%​	0%​
			50	12​	0%​	17%​	25%​	0%​	0%​
			51	13​	0%​	15%​	23%​	0%​	0%​
			52	14​	0%​	14%​	21%​	0%​	0%​
			53	15​	0%​	13%​	20%​	0%​	0%​
			54	16​	0%​	13%​	19%​	25%​	0%​
			55	17​	0%​	12%​	18%​	24%​	0%​
			56	18​	0%​	11%​	17%​	22%​	0%​
			57	19​	0%​	11%​	16%​	21%​	0%​
			58	20​	0%​	10%​	15%​	20%​	25%​

 

[arthurbr]

and it does not work.

What do you mean by "does not work" ? Does it return an error, a wrong value?

 

[KRice]

Try this...I note a couple of things. You want the column range to go from EV:EZ and you need to split the criteria because one deals with determining the row, the other deals with determining the column. Each of those is an argument in the INDEX function. Also, since there is a single array formed from each of the logical comparisons, you'll need to coerce the TRUE/FALSE values to 1's and 0's. I used the double unary operator for that purpose. Another method with SUMPRODUCT is also shown in ER50 that resembles your earlier attempt.

MrExcel_20220719.xlsx
	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ
40		ER	ES	ET	EU	EV	EW	EX	EY	EZ
41	v16	0.55		41		1	2	3	4	5
42	O2 value	2	3	42	4	25%	0%	0%	0%	0%
43	M2 Value	11	8	43	5	20%	0%	0%	0%	0%
44	results	0.18		44	6	17%	0%	0%	0%	0%
45				45	7	14%	0%	0%	0%	0%
46	index/match	0.18		46	8	0%	25%	0%	0%	0%
47				47	9	0%	22%	0%	0%	0%
48	double xlookup	0.18		48	10	0%	20%	0%	0%	0%
49				49	11	0%	18%	0%	0%	0%
50	sumproduct	0.18		50	12	0%	17%	25%	0%	0%
51				51	13	0%	15%	23%	0%	0%
52				52	14	0%	14%	21%	0%	0%
53				53	15	0%	13%	20%	0%	0%
54				54	16	0%	13%	19%	25%	0%
55				55	17	0%	12%	18%	24%	0%
56				56	18	0%	11%	17%	22%	0%
57				57	19	0%	11%	16%	21%	0%
58				58	20	0%	10%	15%	20%	25%
Sheet1

Cell Formulas
Range	Formula
ES42	ES42	=MATCH(ER42,EU41:EZ41,0)
ES43	ES43	=MATCH(ER43,EU42:EU54,0)
ER44	ER44	=INDEX(EU42:EZ54,ES43,ES42)
ER46	ER46	=INDEX(EV42:EZ58,MATCH(1,--(ER43=EU42:EU58),0),MATCH(1,--(ER42=EV41:EZ41),0))
ER48	ER48	=XLOOKUP(ER42,EV41:EZ41,XLOOKUP(ER43,EU42:EU58,EV42:EZ58),"Error",0)
ER50	ER50	=SUMPRODUCT(EV42:EZ58,(EU42:EU58=ER43)*(EV41:EZ41=ER42))

 

[JeffGrant]

HI KRice,

oddly enough I have never used the -- or sumproduct operators. I have use the multiple match criter syntax in several other locations and everything is good. It is just this one location that it doesnt like it.

I do take your point about the EV:EZ range and must admit that I was trying all sorts of things to get the Indez / Double Match to work. I think I just left at at my last attempt when I threw my hands in teh air.

In other locations, what you have idtenified is teh way teh arrays are set up, so thank you for pointing that out.

You have spurred my interest to watch a few youtube videos and learn a bit more about SUMPRODUCT now.

Cheers

 

[KRice]

A few more for options to compare...

1. The index/match comes in two flavors, with one being a simple combination of the expressions developed earlier (see index/match simple substitution in ER45), and the other being index/match binary array where we convert the TRUE/FALSE arrays inside the MATCH function to 1's/0's.
2. More often now, I lean on the new FILTER function available in Excel 365. I've included a nested double FILTER version to demonstrate it.
3. And finally there are mixed variations where one could use the INDEX/MATCH approach to return an entire row or column of interest, and then feed that row or column into the FILTER function to filter out the specific element. I couldn't recommend these approaches when easier to understand and more efficient methods are available. One thing to note about both of these FILTER/INDEX options: inside the INDEX functions there appears to be an odd extra comma. Those commas are critical, as they tell the INDEX function to return the entire row or column, depending on which argument position of the INDEX function is left blank.

MrExcel_20220719.xlsx
	EQ	ER	ES
40		ER	ES
41	v16	0.55
42	O2 value	2	3
43	M2 Value	11	8
44	results	0.18
45	index/match simple substitution	0.18
46	index/match binary arrays	0.18
47
48	double xlookup	0.18
49
50	sumproduct	0.18
51
52	double filter	0.18
53
54	filter/index for row	0.18
55
56	filter/index for column	0.18
Sheet1

Cell Formulas
Range	Formula
ES42	ES42	=MATCH(ER42,EU41:EZ41,0)
ES43	ES43	=MATCH(ER43,EU42:EU54,0)
ER44	ER44	=INDEX(EU42:EZ54,ES43,ES42)
ER45	ER45	=INDEX(EV42:EZ58,MATCH(ER43,EU42:EU58,0),MATCH(ER42,EV41:EZ41,0))
ER46	ER46	=INDEX(EV42:EZ58,MATCH(1,--(ER43=EU42:EU58),0),MATCH(1,--(ER42=EV41:EZ41),0))
ER48	ER48	=XLOOKUP(ER42,EV41:EZ41,XLOOKUP(ER43,EU42:EU58,EV42:EZ58),"Error",0)
ER50	ER50	=SUMPRODUCT(EV42:EZ58,(EU42:EU58=ER43)*(EV41:EZ41=ER42))
ER52	ER52	=FILTER(FILTER(EV42:EZ58,ER43=EU42:EU58),ER42=EV41:EZ41)
ER54	ER54	=FILTER(INDEX(EV42:EZ58,MATCH(ER43,EU42:EU58,0),),ER42=EV41:EZ41)
ER56	ER56	=FILTER(INDEX(EV42:EZ58,,MATCH(ER42,EV41:EZ41,0)),ER43=EU42:EU58)

About the double unary, here is a small illustration of what it does and how it compares to the N function for conditioning an array of logical results to values:

MrExcel_20220719.xlsx
	ER	ES	ET	EU
60	1	TRUE	1	1
61	2	TRUE	1	1
62	3	TRUE	1	1
Sheet1

Cell Formulas
Range	Formula
ER60:ER62	ER60	=SEQUENCE(3)
ES60:ES62	ES60	=ISNUMBER(ER60#)
ET60:ET62	ET60	=--ISNUMBER(ER60#)
EU60:EU62	EU60	=N(ISNUMBER(ER60#))
Dynamic array formulas.

 

[JeffGrant]

Thanks Kirk,

I see the knowledge of the members in this forum and truely wish I was 10 years younger. I am sure that my working carreer would have a taken a completely different path if I was part of the growth of Excel over the last couple of decades.

Thanks for your help and Knowledge.

 

[KRice]

You're welcome, Jeff...I'm happy to help.