Index match multiple columns and true/false statement

[Triarch]

Hello,

I need to find the row that matches the most number of parameters.
I have two datasheets. One table "Table23" with a set of rules that can contain a specific value or a *.
Table23:

In the second table I need to get values and match them against this table to find the truest match and return the value in the Value column.
I have made calculations on each of the row separately to get total matches =($G$2=Table23[@Id1])+($H$2=Table23[@Id2])+($I$2=Table23[@Id3])+($J$2=Table23[@Id4])
and a second calculation to get the true/false value: =AND(OR($G$2=Table23[@Id1];"X"=Table23[@Id1]);OR($H$2=Table23[@Id2];"X"=Table23[@Id2]);OR($I$2=Table23[@Id3];"X"=Table23[@Id3]);OR($J$2=Table23[@Id4];"X"=Table23[@Id4]))

These work perfectly, if i multiply them with eachother i get the desired result per row.

But I want to do this in an array but my logic seems off. The formula I am using is
=INDEX(Table23[Value];MATCH(
MAX(((G2=Table23[Id1])+(H2=Table23[Id2])+(I2=Table23[Id3])+(J2=Table23[Id4]))
*AND(OR(G2=Table23[Id1];"*"=Table23[Id1]);OR(H2=Table23[Id2];"*"=Table23[Id2]);OR(I2=Table23[Id3];"*"=Table23[Id3]);OR(J2=Table23[Id4];"*"=Table23[Id4])));
((G2=Table23[Id1])+(H2=Table23[Id2])+(I2=Table23[Id3])+(J2=Table23[Id4]))
*AND(OR(G2=Table23[Id1];"*"=Table23[Id1]);OR(H2=Table23[Id2];"*"=Table23[Id2]);OR(I2=Table23[Id3];"*"=Table23[Id3]);OR(J2=Table23[Id4];"*"=Table23[Id4]));0);0)

From my own troubleshooting it appears that the true/false statement isn't working per row.

Thankful for any help I could get on this.

Regards, Thomas

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID1	ID2	ID3	ID4	Value		ID1	ID2	ID3	ID4	Value
2	*	*	One	One	50		One	Two	Three	Four	5	5
3	One	*	*	One	2		Ten	Three	Seven	Nine	5	5
4	*	Three	*	One	3		One	Two	Two	Two	4	4
5	One	Two	Two	Two	4		Four	Nine	Six	Five	77	77
6	Two	*	*	*	5
7	Two	*	Three	One	6
8	One	Four	Two	One	7
9	Four	*	*	Five	77
Sheet4

Cell Formulas
Range	Formula
K2:K5	K2	=MOD(MAX(MMULT((G2:J2=Table23[[ID1]:[ID4]])+(Table23[[ID1]:[ID4]]="*"),{1;1;1;1})+Table23[Value]/1000),1)*1000
L2:L5	L2	=INDEX(Table23[Value],MOD(MAX(MMULT((G2:J2=Table23[[ID1]:[ID4]])+(Table23[[ID1]:[ID4]]="*"),{1;1;1;1})+(ROW(Table23[ID1])-ROW(INDEX(Table23[ID1],1))+1)/1000),1)*1000)
Press CTRL+SHIFT+ENTER to enter array formulas.

The K2 formula should work for you, but if the value is non-integer, or even non-numeric, then use the L2 formula. Note that if there is a tie for the amount of matches, then the K2 formula will return the maximum value. The L2 formula will return the value lowest down.

 

[Triarch]

Welcome to the MrExcel forum!

Try:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID1	ID2	ID3	ID4	Value		ID1	ID2	ID3	ID4	Value
2	*	*	One	One	50		One	Two	Three	Four	5	5
3	One	*	*	One	2		Ten	Three	Seven	Nine	5	5
4	*	Three	*	One	3		One	Two	Two	Two	4	4
5	One	Two	Two	Two	4		Four	Nine	Six	Five	77	77
6	Two	*	*	*	5
7	Two	*	Three	One	6
8	One	Four	Two	One	7
9	Four	*	*	Five	77
Sheet4

Cell Formulas
Range	Formula
K2:K5	K2	=MOD(MAX(MMULT((G2:J2=Table23[[ID1]:[ID4]])+(Table23[[ID1]:[ID4]]="*"),{1;1;1;1})+Table23[Value]/1000),1)*1000
L2:L5	L2	=INDEX(Table23[Value],MOD(MAX(MMULT((G2:J2=Table23[[ID1]:[ID4]])+(Table23[[ID1]:[ID4]]="*"),{1;1;1;1})+(ROW(Table23[ID1])-ROW(INDEX(Table23[ID1],1))+1)/1000),1)*1000)
Press CTRL+SHIFT+ENTER to enter array formulas.

The K2 formula should work for you, but if the value is non-integer, or even non-numeric, then use the L2 formula. Note that if there is a tie for the amount of matches, then the K2 formula will return the maximum value. The L2 formula will return the value lowest down.

Thank you for taking the time to look at my problem. Its not quite working as desired. I expect searched rows that have a specific value that isnt search value or * to be skipped or nullified. I would expect K3, K4, L3 and L4 to be #VALUE! or return row 0 which I can then make sure is desired value in row 1 of table23.

 

[Triarch]

Thank you for taking the time to look at my problem. Its not quite working as desired. I expect searched rows that have a specific value that isnt search value or * to be skipped or nullified. I would expect K3, K4, L3 and L4 to be #VALUE! or return row 0 which I can then make sure is desired value in row 1 of table23.

Meant K2, K3, L2 and L3.

 

[Eric W]

Sorry, I misunderstood. A "*" generally means "match anything". Try these versions:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID1	ID2	ID3	ID4	Value		ID1	ID2	ID3	ID4	Value
2	*	*	One	One	50		One	Two	Three	Four	4	4
3	One	*	*	One	2		Ten	Three	Seven	Nine	3	3
4	*	Three	*	One	3		One	Two	Two	Two	4	4
5	One	Two	Two	Two	4		Four	Nine	Six	Five	77	77
6	Two	*	*	*	5		Pi	Rho	Sigma	Gamma	50	50
7	Two	*	Three	One	6
8	One	Four	Two	One	7
9	Four	*	*	Five	77
Sheet4

Cell Formulas
Range	Formula
K2:K6	K2	=IFERROR(MOD(AGGREGATE(14,6,1/(1/MMULT((G2:J2=Table23[[ID1]:[ID4]])+0,{1;1;1;1}))+Table23[Value]/1000,1),1)*1000,INDEX(Table23[Value],1))
L2:L6	L2	=IFERROR(INDEX(Table23[Value],MOD(AGGREGATE(14,6,1/(1/MMULT((G2:J2=Table23[[ID1]:[ID4]])+0,{1;1;1;1}))+(ROW(Table23[ID1])-ROW(INDEX(Table23[ID1],1))+1)/1000,1),1)*1000),INDEX(Table23[Value],1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Same idea, but it won't match on * (or whatever you use to denote "no compare"). Also, if there are no matches, it will default to the value in the first row of Table23. You can change that to whatever you want by changing the second parameter of the IFERROR function. See row 6 for a test example.

 

[Triarch]

I understand that the * can be confusing. They are used as wildcards in the software its taken from but could just aswell be blank in this case or replace with any other specific value.
Thank you for all your time but the formulas are still not functioning as desired.

In your example I expect K2, K3, L2 and L3 to not return a match, (or match line 0 of index). (lets replace * with X for clarity)
If Table23[[Id1] <> Id1 or "X" then that row should never be used in the match, same for Id2-4.

So I am searching for a way to find the line that matches the most criteria but excluding rows that has 1 or more set values that doesn't match.

 

[Eric W]

OK, I'm confused. Let's look at the first example, One, Two, Three, Four. With a 1 for "match", and 0 for "no match", here's how it compares with table23:
0000
1000
0000
1100
0000
0010
1000
0000

3 rows have 1 match, 1 row has 2 matches, which is the most matches, so the formula returns the value from row 4. But you're saying you want to exclude any row with any mismatch (0)? If that's the case, then you only want the rows with all four IDs matching. Then except for my third example, they all should return the default. When you say "exclude any row with 1 or more mismatches", and "pick the row that matches the most criteria", that seems to be a contradiction. What am I missing?

 

[Triarch]

The missmatches should only be excluded if there is a specific value.
So if Id1 and Id3 matches but Id2 and Id4 is filled by any value other than "X" than that entire row should be counted as 0 match. If Id2 and Id4 in this scenario is filled by "X" than the 2 matches still count.

 

[Eric W]

All right, try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID1	ID2	ID3	ID4	Value		ID12	ID23	ID34	ID45	Value6
2	X	X	One	One	50		One	Two	Three	Four	50	50
3	One	X	X	One	2		Ten	Three	Seven	Nine	50	50
4	X	Three	X	One	3		One	Two	Two	Two	4	4
5	One	Two	Two	Two	4		Four	Nine	Six	Five	77	77
6	Two	X	X	X	5		Pi	Rho	Sigma	Gamma	50	50
7	Two	X	Three	One	6		Two	Nine	Three	One	6	6
8	One	Four	Two	One	7		Two	Nine	Three	Six	5	5
9	Four	X	X	Five	77
Sheet1

Cell Formulas
Range	Formula
K2:K8	K2	=IFERROR(MOD(AGGREGATE(14,6,EXP(LN(MMULT(IF(G2:J2=Table23[[ID1]:[ID4]],1,IF(Table23[[ID1]:[ID4]]<>"X",-10,0)),{1;1;1;1})))+Table23[Value]/1000,1),1)*1000,INDEX(Table23[Value],1))
L2:L8	L2	=IFERROR(INDEX(Table23[Value],MOD(AGGREGATE(14,6,EXP(LN(MMULT(IF(G2:J2=Table23[[ID1]:[ID4]],1,IF(Table23[[ID1]:[ID4]]<>"X",-10,0)),{1;1;1;1})))+(ROW(Table23[Value])-ROW(INDEX(Table23[Value],1))+1)/1000,1),1)*1000),INDEX(Table23[Value],1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Triarch]

Based on your examples both seem to work as desired. K2 formula only returns row one for me but L2 works perfectly in this test version. Time to put it to test in the real sheet. Thank you very much for all your help