0

Sheet A

The Sheet A contains data in a single column

enter image description here

Sheet B contains values in multiple columns and rows

enter image description here

I would like to find out if the value in Sheet A is present in Sheet B. If the value exists, it should result as 'match'. If the value doesn't exists then should result as 'No match

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Newbie
  • 21
  • 3
  • You can try to transform the data in sheet B into one column list: https://stackoverflow.com/questions/72093701/excel-append-one-full-range-to-the-end-of-another-in-one-full-column or TEXTJOIN all cells into one string to be searched against (depending on the size of the range: “ If the resulting string exceeds 32767 characters (cell limit), TEXTJOIN returns the #VALUE! error.”) – Anonymous Jun 20 '22 at 21:26

1 Answers1

1

You may try this way, using either SUM() or SUMPRODUCT() or MAX() with ROW() Function

enter image description here

• Formula used in cell B2

=--(MAX((A2='Sheet B'!$A$2:$F$7)*ROW('Sheet B'!$A$2:$F$7))>0)

The above formula returns a Boolean Logic which is turned into 1 & 0 using a double unary. And then custom formatted as

[=1]"Match";[=0]"No Match";

• Perhaps you can wrap the above within an IF() as well, in cell C2

=IF(MAX((A2='Sheet B'!$A$2:$F$7)*ROW('Sheet B'!$A$2:$F$7))>0,"Match","No Match")

Note: Since its an array formula, based on your excel version need to press CTRL+SHIFT+ENTER , O365 & Excel 2021 Users don't need to press CTRL+SHIFT+ENTER!

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32