CODEWITHSUNDEEP
excelexcel-formulavlookuplookupxlookup
Newbie
Newbie

Reputation: 37

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

Upvotes: 0

Views: 136

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

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!

Upvotes: 1

Related Questions