Forum Discussion

surefirewizard's avatar
surefirewizard
Copper Contributor
Dec 09, 2021

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...
Formulas and Functions
surefirewizard's avatar
surefirewizard
Copper Contributor
Dec 09, 2021
Also, I don't have XMATCH on my version of Excel. Can you provide the code with ranges and MATCH?
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 09, 2021

surefirewizard 

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.

Resources