Loop through 2 columns and and return TRUE or FALSE if adjscent cells are matching.

[strongman86]

Hi lads,

Need formula in cell E1 that loops through Columns A & C and if cell value is identical i.e. AB1234X vs AB1234X then it need to compare corresponding adjscent cell values in column B & D to see if they are matching. If they are matching then it needs to return TRUE if not matching then FALSE.



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AB1234X[/TD]
[TD]2.40[/TD]
[TD]AD2456Y[/TD]
[TD]0.24[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AU1336X[/TD]
[TD]1.20[/TD]
[TD]AB1234X[/TD]
[TD]2.40[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AD2456Y[/TD]
[TD]0.20[/TD]
[TD]ZN3590Q[/TD]
[TD]1.54[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]TR3456U[/TD]
[TD]5.50[/TD]
[TD]AU1336X[/TD]
[TD]1.20[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ZN3590Q[/TD]
[TD]1.54[/TD]
[TD]TR3456U[/TD]
[TD]5.52[/TD]
[TD]FALSE

[/TD]
[/TR]
</tbody>[/TABLE]


Regards,
Strongman86

 

[Fluff]

How about
=ISNUMBER(MATCH(C2&"|"&D2,INDEX($A$2:$A$6&"|"&$B$2:$B$6,0,0),0))

 

[Jon von der Heyden]

Try:

=IFERROR(VLOOKUP(C1,A:B,2,0)=D1,FALSE)

 

[strongman86]

Hi Fluff, Works ok, but I've noticed sometimes some cells in column C has space after text and then formula doesn't work. Can you amend formula so it looks only for first 7 characters? Thanks.

 

[Fluff]

How about
=ISNUMBER(MATCH(LEFT(C2,7)&"|"&D2,INDEX(LEFT($A$2:$A$6,7)&"|"&$B$2:$B$6,0,0),0))

 

[strongman86]

Perfect. Thanks Fluff.

 

[Rick Rothstein]

Hi Fluff, Works ok, but I've noticed sometimes some cells in column C has space after text and then formula doesn't work. Can you amend formula so it looks only for first 7 characters? Thanks.

If the spaces could only be in Column C (not Column A) then this formula should work for you...

=COUNTIFS(A$1:A$5,TRIM(C1),B$1:B$5,D1)>0

Adjust the 5 in the A$5 and B$5 to be the last row number for your data.

 

[Fluff]

Perfect. Thanks Fluff.

You're welcome & thanks for the feedback