Updating formula to match result in a different column/row

[Brew]

How do I update the formula below to give me the desired result in example 4 with finding the matching values as it does with examples 1,2,and 3:

Example 4:

if df8:dh8=345 and
if df7:dh7=234 then
di7=3,4 (because df8=dg7 and dg8=dh7) this is the desired result
but the formula did not yield any matches in this case

The formula below works for the following example:
=SUBSTITUTE(SUBSTITUTE(IF(DF7=DF8,DF7," ")&","&IF(DG7=DG8,DG7," ")&","&IF(DH7=DH8,DH7," "),", ","")," ,","")

example1

if df8:dh8=124 and
if df7:dh7=125 then
di7=1,2 (because df8=df7 and dg8=dg7

example2

if df8:dh8=125 and
if df7:dh7=135 then
di7=1,5 (because df8=df7 and dh8=dh7

example3

if df8:dh8=115 and
if df7:dh7=134 then
di7=1 (because df8=df7

 

[Brew]

The matching values will only be 1, 2, 3, 4, or 5.....Any ideas on how to update the formula

 

[Domenic]

Try...

=IF(LEN(DF7&DG7&DH7)>LEN(SUBSTITUTE(DF7&DG7&DH7,DF8,"",1)),DF8,"")&IF(LEN(SUBSTITUTE(DF7&DG7&DH7,DF8,"",1))>LEN(SUBSTITUTE(SUBSTITUTE(DF7&DG7&DH7,DF8,"",1),DG8,"",1)),DG8,"")&IF(LEN(SUBSTITUTE(SUBSTITUTE(DF7&DG7&DH7,DF8,"",1),DG8,"",1))>LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DF7&DG7&DH7,DF8,"",1),DG8,"",1),DH8,"",1)),DH8,"")

If you want the digits separated by commas, try the following which uses helper cells...

DJ7:

=IF(LEN(DF7&DG7&DH7)>LEN(SUBSTITUTE(DF7&DG7&DH7,DF8,"",1)),DF8,"")

DK7:

=IF(LEN(SUBSTITUTE(DF7&DG7&DH7,DF8,"",1))>LEN(SUBSTITUTE(SUBSTITUTE(DF7&DG7&DH7,DF8,"",1),DG8,"",1)),DG8,"")

DL7:

=IF(LEN(SUBSTITUTE(SUBSTITUTE(DF7&DG7&DH7,DF8,"",1),DG8,"",1))>LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DF7&DG7&DH7,DF8,"",1),DG8,"",1),DH8,"",1)),DH8,"")

DM7:

=IF(DJ7<>"",DJ7,"")&IF(DK7<>"",IF(DJ7<>"",", "&DK7,DK7),"")&IF(DL7<>"",IF(OR(DJ7<>"",DK7<>""),", "&DL7,DL7),"")

Hope this helps!

 

[Brew]

Thaks, This formula gives the accurate results, but without commas. Is there a way to separate cell results with spaces or commas in the formula without using additional helper cells?

 

[Domenic]

How about defining a name for each part of the formula (DJ7, DK7, and DL7), and using the defined names for the formula in DM7...

 

[Brew]

I suppose that would work if I understand correctly, The 3 main elemental functions of the formula would be rename as "A" , "B" and "C" and imbedding into the format of the formula in DM7 as one larger formula, if so, how do we set it up?

 

[Domenic]

Try...

Insert > Name > Define

Name: A

Refers to:

=IF(LEN($DF$7&$DG$7&$DH$7)>LEN(SUBSTITUTE($DF$7&$DG$7&$DH$7,$DF$8,"",1)),$DF$8,"")

Click Add

Name: B

Refers to:

=IF(LEN(SUBSTITUTE($DF$7&$DG$7&$DH$7,$DF$8,"",1))>LEN(SUBSTITUTE(SUBSTITUTE($DF$7&$DG$7&$DH$7,$DF$8,"",1),$DG$8,"",1)),$DG$8,"")

Click Add

Name: C

Refers to:

=IF(LEN(SUBSTITUTE(SUBSTITUTE($DF$7&$DG$7&$DH$7,$DF$8,"",1),$DG$8,"",1))>LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($DF$7&$DG$7&$DH$7,$DF$8,"",1),$DG$8,"",1),$DH$8,"",1)),$DH$8,"")

Click Ok

Then try...

=IF(A<>"",A,"")&IF(B<>"",IF(A<>"",", "&B,B),"")&IF(C<>"",IF(OR(A<>"",B<>""),", "&C,C),"")

Hope this helps!

 

[Brew]

OOOOO....I C..., so is there not a way to use one formula in one cell, that would produce the same results and use comma separators, without dividing the formula into cells and referencing into yet another cell to produce the desired result?

 

[Domenic]

OOOOO....I C..., so is there not a way to use one formula in one cell, that would produce the same results and use comma separators, without dividing the formula into cells and referencing into yet another cell to produce the desired result?

Not that I'm aware of. But, in effect, while 3 references are defined, only one formula will be placed onto the worksheet itself. And, if the formula needs to be copied to other cells, select the first cell in the range before defining the references, and change to a relative reference, accordingly.

Won't this do?

 

[Brew]

ok thanks