Matching Data

[ithoughtiknewit]

Hello!

I am trying to take a list I receive from a travel agent and compare it to a list of assignments to see who may not have booked travel. Column A has the travel agent data, and column C has data from our scheduling team. Internally, we don't use middle names, and the formatting is very different. Thoughts on how I can find a match if they booked? Thanks as always!!

 

[SanjayGMusafir]

I don't use Excel 365 but can give you the idea -

Steps within LET function

1. Use TextAfter to extract name after period, and
2. TextBefore to extract name before "/"
3. Textjoin the above 2
4. Use the result to search using XLOOKUP or as per the data set

It shall look something like -

=LET(A,Textafter(),B,Textbefore(),C,Textjoin(" ",A,B),XLOOKUP(C,...))