Search code examples
excelexcel-formulavlookuplookupxlookup

Lookup values from Sheet A with data at Sheet B. The data at sheet B is located in multiple columns

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

Solution

  • 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!