Trying to "fuzzy" an xlookup

[FrumpyJones]

Hi big brains ,

I know enough to get slightly dangerous (And definitely enough to get into trouble), but I definitely don't know enough enough. Need help with the following:

I have a formula I'm using that will look at the agent name in b2 and then look at a hidden tab that has all the names of all the agents and who their supervisor is. So, xlookup was the way I went. The formula looks like this:

=IF(ISBLANK(B2),"",IF(ISBLANK(XLOOKUP(B2,'Agent-Supervisor'!$A:$A,'Agent-Supervisor'!$B:$B,"Agent Not Found",2)),"Supervisor Not Found",XLOOKUP(B2,'Agent-Supervisor'!$A:$A,'Agent-Supervisor'!$B:$B,"Agent Not Found",2)))

Thing is some of our agents have three names and for whatever reason, when people are copying them from this one database and putting it into this spreadsheet, it only has two, so it's returning an AGENT NOT FOUND. Is there a way to FUZZY this formula so as long as it contains all the words in b2, it delivers the corresponding supervisor value?

Example: In the lookup tab my name is Frumpy Bartholomew Jones, but someone just put Frumpy Jones into the spreadsheet, so I get the AGENT NOT FOUND. Looking for it to say as long as ALL of the words in B2 appear in a singular cell in column A, use it.

Thanks

 

[alansidman]

Power Query which is part of Excel allows for matching fields with a fuzzy match. You might want to look into this approach.

 

[FrumpyJones]

Power Query which is part of Excel allows for matching fields with a fuzzy match. You might want to look into this approach.

Thank you for the suggestion, but I work for a large company that has everything locked down pretty hard. I am unable to download and install add-ins.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this modification.

=LET(a,'Agent-Supervisor'!$A:$A,b,'Agent-Supervisor'!$B:$B,n,SUBSTITUTE(B2," ","*"),IF(B2="","",IF(XLOOKUP(n,a,b,"Agent Not Found",2)="","Supervisor Not Found",XLOOKUP(n,a,b,"Agent Not Found",2))))

 

[alansidman]

Power Query is part of Excel versions starting with 2016 and no downloads are needed. As Peter suggested, please update your profile to reflect your version of excel so that we can offer workable solutions

 

[Peter_SSs]

Try this modification

Here is a shorter version that I think does the same job

=LET(t,IF(B2="",1,XLOOKUP(SUBSTITUTE(B2," ","*"),'Agent-Supervisor'!$A:$A,'Agent-Supervisor'!$B:$B,2,2)),SWITCH(t,0,"Supervisor not found",1,"",2,"Agent not found",t,t))

 

[FrumpyJones]

Here is a shorter version that I think does the same job

=LET(t,IF(B2="",1,XLOOKUP(SUBSTITUTE(B2," ","*"),'Agent-Supervisor'!$A:$A,'Agent-Supervisor'!$B:$B,2,2)),SWITCH(t,0,"Supervisor not found",1,"",2,"Agent not found",t,t))

Thank you for the suggestion about updating my profile. I have done that.

I tried both versions you gave me and I'm still getting Agent not found. An exact example:

In agent-supervisor The agent name is Crystal Smith Jones
In the main sheet it's showing Crystal Jones and I'm getting the Agent Not found.
If I remove "smith" from the agent-supervisor, it's then placing the supervisor name correctly.

 

[FrumpyJones]

Power Query is part of Excel versions starting with 2016 and no downloads are needed. As Peter suggested, please update your profile to reflect your version of excel so that we can offer workable solutions

Ah. When I followed the link you originally sent it talked about downloading it and I knew I was not going further. I did find it by searching for it. Now I have to kick the tires on this thing and figure out how it works. Thank you.

 

[alansidman]

did you look at this link I provided in my signature

The Complete Guide to Power Query | How To Excel

www.howtoexcel.org

 

[FrumpyJones]

did you look at this link I provided in my signature

The Complete Guide to Power Query | How To Excel

www.howtoexcel.org

I will look at it again now. I went and found a youtube video that gave me a quick introduction, and I think I got most of the basics now, but hoping your link (which is extensive ) will provide me with the answers to some of my lingering questions, which are:
1) This spreadsheet is going to be used by people entering more rows as time goes by.

a) Is the power query going to automagically work a week from now when 50+ rows have been added to the main data tab by multiple others?(Which I have converted to a table)?​

b) Will the Power query automagically work when I update the agent-supervisor tab with the latest data (Also in a table)​

2) How do I get the the merge data I'm creating to load into column C of the main data sheet

I'll probably be back here to ask for help soon