CODEWITHSUNDEEP
excelexcel-formulavlookup
Bewing
Bewing

Reputation: 13

Excel - Check if two values exist together in two columns

I want to be able to check if user in B2 has access to applications in A3:A6.

I have a list of all users and what they have access too, how will I be able to match and compare both lists and place a Yes or No in the table?

Please see the example from the screenshot below:

Example

I've had a look at VLookups etc but any help wouldn't go a miss!

Upvotes: 1

Views: 2664

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use this array formula:

=IF(ISNUMBER(MATCH(B$1&$A2,$H$2:$H$15&$G$2:$G$15,0)),"Y","N")

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

enter image description here

Upvotes: 2

Related Questions