0

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

4
  • Welcome to Web Applications. Please show what you tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Mar 9 at 21:45
  • 1
    Keep in mind that we cannot see what you are looking at. So while your data and your goal are clear to you, they are very unclear for those of us "out here." The most efficient and effective way to receive help is to share a link to your spreadsheet (or to a sample spreadsheet with enough realistic data to represent the problem and goal), and to manually enter the results you'd like to see. Indicate where the results should go and how you decided what to type there. Once we can see it all, I'm sure somehow here can quickly help you solve the problem.
    – Erik Tyler
    Mar 10 at 12:57
  • @ErikTyler Thank you so much for your feedback i added an example sheet of what i'm trying to do. Mar 10 at 15:25
  • @Rubén Thank you i have added more information to what i have tried and what i'm trying to accomplish. Mar 10 at 15:27
0

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."

2
  • Thank you so much for your help the more you know Mar 10 at 16:58
  • You're welcome, Ismar.
    – Erik Tyler
    Mar 10 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.