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]

Be aware that if you use more than 4 ID columns, the formula will have to be changed a bit. But I'm glad I could help, thanks for the feedback.

 

[Triarch]

I Will need to use it for 3-6 match values in different searches. How Will it need to be altered?

 

[Eric W]

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

The array constant (in red) needs to have a 1 for each column. 4 columns, 4 1s. The blue -10 needs to be bigger in magnitude than the number of columns you're comparing. So if you go to 6, it won't have to change. And of course, the ranges (G2:J2) will have to change, as well as the Table references.