Formula for matching multiple row cell including blank cell

0
votes

Well, my question is, What is the formula for matching multiple row cell, if Match TRUE else FALSE then show the different row cell value in next column also sheet have blanks cell too so formula avoid blank cell and result is TRUE.

Please follow the sheet

enter image description here

I am using this formula:

{=AND(EXACT(A2:M2,A2))}

and this formula:

=IF(COUNTIF(A2:M2,A2)=13,"TRUE","FALSE")

But it match with blank cell, I need formula to only for existed matching row cell value then result is TRUE and FALSE. and if False then show that row cell column value in next column

Thanks BN

1
vbaexcelformulas
if I understand your question correctly, I tested this and it works: =IF(COUNTIF(A2:M2,A2)+COUNTIF(B2:M2,"")=13,TRUE,FALSE). It counts the number of times the row header appears and then counts the blanks, and if the sum of the two equal 13, you know its an exact match, taking blanks into consideration. – Scott Holtzman
Thanks @ScottHoltzman – user3525945

1 Answers

2
votes

The three standard formulas use in M2:O2 in the following image are,

=COUNTIF(A2:L2, A2)=COUNTA(A2:L2)
=IFERROR(INDEX(A2:L2, AGGREGATE(15, 6, COLUMN(A:L)/((A2:L2<>A2)*SIGN(LEN(A2:L2))), 1)), "")
=IFERROR(INDEX(A$1:L$1, AGGREGATE(15, 6, COLUMN(A:L)/((A2:L2<>A2)*SIGN(LEN(A2:L2))), 1)), "")

Fill down as necessary.

   ROGUE_ENTRIES

The conditional formatting rule for B2:L9 was based upon the following formula,

=AND(LEN(B2), B2<>$A2)