Forum Discussion
Comparing Two Worksheets, please help!
I have two worksheets in a workbook, 'Master' and 'TB43-180'. I need something that will check each line in the Master against each line in TB43-180, such that if the model, nomenclature, and...
It looks like you have structured tables in two sheets
You may add to Master another column (Test) with formula in it like
=IF( ISNA(
XMATCH( [@Model]&[@Nomenclature]&[@Syscode]&[@Intvl],
TB43_180[Model]&TB43_180[Nomenclature]&TB43_180[Syscode]&TB43_180[Intvl]) ),
"not match", "" )and apply conditional formatting to highlight not matched records.
Similar could be done with ranges and MATCH().
- Also, I don't have XMATCH on my version of Excel. Can you provide the code with ranges and MATCH?
With MATCH() just add to it third parameter as zero:
=IF( ISNA( MATCH( [@Model]&[@Nomenclature]&[@Syscode]&[@Intvl], TB43_180[Model] & TB43_180[Nomenclature] & TB43_180[Syscode] & TB43_180[Intvl], 0 ) ), "not match", "" )I'm almost sure you have structured tables. But if ranges it depends on how data is structured exactly, plus it'll be better to use dynamic range. In simple case with hardcoding ranges
=IF( ISNA( MATCH( $C3 & $D3 & $E3 & $F3, 'TB43-180'!$B$5:$B$16 & 'TB43-180'!$C$5:$C$16 & 'TB43-180'!$D$5:$D$16 & 'TB43-180'!$E$5:$E$16, 0 ) ), "not match", "" )and drag or Ctrl+D such cell down.
SergeiBaklan Thanks, figured it out on my own but your solution is much more elegant!
