How compare 3 columns and put yes or no in another

问题:

Hi,

I want to compare six column, meterial with material (a with e), plant with plant and pret with pret, if this columns are equals put in rezultat yes, if not put no.

Ex. We have Material 1, Plant ANG, Pret 4.5, i want to search in E meterial 1, plang ANG and compare pret, if pret is equals then put in Rezultat OK.

Thanks a lot

回答1:

=IF(AND(INDEX(F:F, MATCH(A2,E:E, 0))=B2,INDEX(G:G, MATCH(A2,E:E, 0))=C2), "OK", "Not OK")

This will work but only if there are no duplicate values.

回答2:

This works with duplicate values

The formula will need to be entered as an array (once copy and pasted while still in the formula bar hit CTRL+SHIFT+ENTER)

=IF(IFERROR(INDEX($E$2:G$47,MATCH(1,(A2=$E$2:$E$47)*(B2=$F$2:$F$47)*(C2=$G$2:$G$47),0),3),FALSE),"OK","")

This will return "OK" only when all 3 conditions are met even where the material number appears multiple times within the data. The cell will stay blank if no match is found.

If you want to identify the number of times all 3 columns match

Consider SumProduct, let me know if you need some help with it and I will update this answer with the SumProduct formula needed

回答3:

You can use formula

in I2 =SUMPRODUCT(--(A2=$E$2:$E$16),--(B2=$F$2:$F$16),--(C2=$G$2:$G$16))

in I3 =SUMPRODUCT(--(A3=$E$2:$E$16),--(B3=$F$2:$F$16),--(C3=$G$2:$G$16))

etc.