In Google Sheets, how do I search for a value in lists in multiple sheets, and return a result if there is at least one match?

Solution 1:

A More General Problem

To make this more general so others find it useful (which is the whole point of StackOverflow), the general problem can be rephrased as

"How do I search for a value in lists in multiple sheets, and return a result if there is at least one match?"

Generally, when using VLOOKUP(), QUERY(), or other matching functions, you have to account for any errors through non-matches. Those "move" outwards into the outer functions and can eventually be the reported result, unless explicitly handled. Sometimes, this is less obvious when you sometimes get answers but that's because the outer functions have ignored or not evaluated the matching function.

Therefore, always consider what happens if the matching function returns a N/A and explicitly handle it.

Your Example

In your case, IFS() is simply raising an error whenever VLOOKUP() does not match. However, since IFS() returns the first condition that matches, from left to right in the formula, it doesn't always get around to evaluating one of the non-matching VLOOKUP()s which is why you see it works sometimes.

So, you should explicitly handle the errors with e.g. IFERROR()

My approach was to avoid lookup functions, and just filter the list by those that had filled checkboxes, and then count the occurrence of interest ("Company 1", "Company 2", ...) using COUNTIF(). If the counted total is 1, we have a match, so grab that entry as an element in an array. Otherwise, leave an empty value.

At this point, you could drop the empty elements, and take the first non-empty element (or return a blank), but I opted to list out all of the names.

To get rid of the blanks, I use QUERY() and then JOIN() to make a list of each name. In the case, where nothing matched, and my array was empty, I simply wrap everything in one IFERROR().

=IFERROR(JOIN(", ",QUERY(TRANSPOSE({IF(COUNTIF(FILTER(Sam!A:A,Sam!B:B=TRUE),A2)=1,"Sam",""),IF(COUNTIF(FILTER(Nick!A:A,Nick!B:B=TRUE),A2)=1,"Nick",""),IF(COUNTIF(FILTER(Mike!A:A,Mike!B:B=TRUE),A2)=1,"Mike","")}),"SELECT Col1 WHERE Col1 IS NOT NULL",0)),"")

I found it convenient for your example to rename "Sheet2" as "Sam", "Sheet3" as "Nick", "Sheet4" as "Mike", which is what I think was your original meaning.

The formula can be easily modified to show just the first matching result.