Google Sheets – Compare an Array or List to One Cell

google sheets

I have a question about Google Sheets. So I have a pre generated list with a manager name and client name and a weekly generated lists with the client name. I want to be able to compare both lists and when a match is found for the manager name to be filled on the row where the match was found. I don't know what would be the simplest way to achieve this.

Here is what I have tried so far:

This is how client cells look:

Bassman, Harley & Lori:Bassman, Harley & Lori-P/M Svc:D-Bassman, High ceiling Separation, 1-26-20

So I used =SPLIT(E4 , " ") to split apart everything but the last name so for the above cell I get left with Bassman,

Then I compared the left over with this statement =IF(OR(O4 = "Campbell,", O4 = "Campbell"), "Mike S", " ")

I found a couple of examples but I cant wrap my head around how to make this work. I had originally thought about making a loop but I think the built in functions would be easier to work with. What i can't figure out is how do i iterate through all the possible managers.

Here is an example sheet of what i'm trying to accomplish.
Sheet Here

Best Answer

I have added two new sheets to your spreadsheet ("Erik Help" and "Client-Manager"). These two sheets work in tandem (i.e., "Erik Help" references "Client-Manager").

It is not clear to me why some of your clients are listed as last name alone, some are listed as last name and first initial, and some are listed as last name and full first name. But in looking through your list, it didn't seem that anything but the last name actually determines the manager, so I worked with just that piece of information.

There is a single formula, highlighted in bright green, in 'Erik Help'!H2. This formula produces the header and all results for that column:

=ArrayFormula({"Manager";IF(E3:E="",,IFERROR(VLOOKUP("*"&REGEXEXTRACT(E3:E,"[a-zA-Z]+")&"*",'Client-Manager'!A:B,2,FALSE),"NO MATCH"))})

Understand that when an array formula "owns" a range or column, you can no longer manually enter data into that range/column without breaking the array and having the formula return an error.

The REGEXEXTRACT extracts the last name from Column E.

The VLOOKUP wraps this in wildcard characters (the asterisk) to that this last name will match all variations you currently have in the client column in the 'Client-Manager' sheet.

If a match is found, the column-2 result (i.e., the manager's name) is returned.

If no match is found, IFERROR gives the message "NO MATCH."

IF(E3:E="",, just means "don't put anything here if there is nothing in Column E."

Related Topic