Using multiple sheets in Excel workbook to perform an audit

Question

I have an Excel spreadsheet that I am using to perform an audit.

On Sheet1 there are five columns: Name, Mobile Number, Group1, Group2. The Name column is populated already

On Sheet2 there are three columns: Group, Name, Number, which are all populated.

On Sheet1, I want to do the following:

1. Populate the mobile number for each name by checking if that name is on Sheet2 and if so, return the mobile number, if not then I want it to return Not Found.

I have had some success using XLOOKUP for this action, some return #N/A due to formatting, and I am working through options to clean the name data using CLEAN/TRIM etc.

2. For each of the Group columns, I want to query Sheet2 and see if the person on any given row is in the group, and return Yes if present, and No if not.

I am unsure how to achieve action 2, the lookup would need to check two criteria are true - ie. The name, and the group and if both are true then return either Yes/No (or TRUE/FALSE etc).

I'm not super skilled in Excel and I've been experimenting with VLOOKUP, XLOOKUP, INDEX/MATCH and so far have been unable to achieve what I want.

Any help would be greatly appreciated!

Answer 1

Lets start with retrieving the mobile number as that is the most complex:

=IF(
    COUNTIFS($G$3:$G$6,$A3,$H$3:$H$6,"<>")=0,
    "Not Found",
    INDEX($H$3:$H$6,MATCH(1,INDEX((($G$3:$G$6=$A3)*($H$3:$H$6<>"")),0)))
)

The COUNTIFS is checking how many non-blank phone numbers appear for that name in sheet 2. If there are no numbers, the if statement returns "Not Found". If there is at least one number, we use INDEX/MATCH/INDEX to return the first non-blank value.

This looks a bit complex but it is basically a standard INDEX/MATCH formula, with an additional inner INDEX, which allows us to specify multiple criteria, which is required to filter out any possible blank phone numbers. If we were sure that all the Mobile Numbers were actually numbers (rather than text), we could use a simpler MAXIFS function.

After that, returning the groups is easy:

=IF(COUNTIFS($G$3:$G$6,$A3,$F$3:$F$6,1)>0,"Yes","")

for Group 1. And:

=IF(COUNTIFS($G$3:$G$6,$A3,$F$3:$F$6,2)>0,"Yes","")

for group 2.

These simply count the number of occurrences of the group for a particular name. If it is more then 0, it returns "Yes", otherwise it remains empty.