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

 

[FrumpyJones]

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

@alansidman ,

So I got power query to work (Test data in the screencap below), and I realize my questions I asked above are reversed. Getting the answer to #2 will lead to an answer for #1. So here's the screencap:

The first two columns are my main data sheet, with the supervisor column running the xlookup I originally made. You can see some AGENT NOT FOUND results. Column C is the power query result where it works wonderfully at matching different types of fuzziness. So how do I get this result to replace column C in the main worksheet, and once I know how to do that, I'll get my answer as to if adding new rows will automagically update itself.

Thank you again for your help.. I'm inching closer to finish line

 

[alansidman]

Any updates to your source table (changes or additional data) will update automatically. All you need to do is click on Data, Refresh All and the Query will update.
Close and Load your data to where you wish the output to go.

 

[Peter_SSs]

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.

It works for me

FrumpyJones.xlsm
	A	B
4	Crystal Smith Jones	Sammy
Agent-Supervisor

FrumpyJones.xlsm
	B	C
4	Crystal Jones	Sammy
Main

Cell Formulas
Range	Formula
C4	C4	=LET(a,'Agent-Supervisor'!$A:$A,b,'Agent-Supervisor'!$B:$B,n,SUBSTITUTE(B4," ","*"),IF(B4="","",IF(XLOOKUP(n,a,b,"Agent Not Found",2)="","Supervisor Not Found",XLOOKUP(n,a,b,"Agent Not Found",2))))

I suspect that what is happening is that the space characters in your data are not standard space characters.
Do this test on each sheet. Put this formula in a vacant cell and point it at the cell on that sheet that contains Crystal's name & see what it returns.

=CODE(MID(A4,8,1))

Standard spaces would return 32 in each case.

If they do both return 32 then on the 'Agent-Supervisor' sheet try this pointed at Crystal's cell. Does it return 19?

=LEN(A4)