EXCEL: Lookup characters, regardless of order?

[DarkJester89]

Is this a possibility, to look up characters, regardless of order in cell?

Workbook:

If I'm trying to find John Smith:

if the list has Smith John, Smith John A, and John A. Smith, it'll still find it?

=NOT(ISNA(MATCH(TRIM(SUBSTITUTE(F2,CHAR(160)," ")),TRIM(SUBSTITUTE($D$2:$D$997,CHAR(160)," ")),0)))

^Array formula

Thank in advance.

 

[Rick Rothstein]

Assuming the search text in cell F2 will always only be two "words" separated by a space, does this formula do what you want...

=NOT(ISERROR(IFERROR(SEARCH(SUBSTITUTE(F$2," ","*"),D2),SEARCH(MID(F$2&"*"&F$2,FIND(" ",F$2)+1,LEN(F$2)),D2))))

 

[DarkJester89]

no, it may be a variety of things, i heard vba has a fuzzy match ill be looking into

 

[Rick Rothstein]

no, it may be a variety of things...

Given we know nothing about your data, it would be kind of hard for us to guess what you mean by this. Can you post a fairly representative sample of whatever it is you are referring to here?

...i heard vba has a fuzzy match ill be looking into

You are probably referring to the Like operator. I would point out base on how you presented your first post that it appeared you were looking for a formula solution which is why I posted what I did. If you give us the example text that I asked for above, we may be in a better position to show you how to apply the Like operator to them.

 

[DarkJester89]

The screenshots are examples of the data and I linked the workbook example.

User input is various methods of

First Last
Last, First
First M. Last
Last First
Last First M.

I've redirected user input into First Last in a new cell but I can't edit this previously @Rick Rothstein

 

[Eric W]

You might be thinking of the Fuzzy match tool described here:

Excel 2020: Use the Fuzzy Lookup Tool from Microsoft Labs

When you use VLOOKUP, HLOOKUP, or INDEX/MATCH, Excel is expecting an exact match. But in real life, data is messy. Several years ago, the research team at Microsoft Labs released a free Fuzzy Lookup add-in.

www.mrexcel.com

 

[Rick Rothstein]

User input is various methods of

First Last
Last, First
First M. Last
Last First
Last First M.

Okay, I see a problem. If you do not always use a comma when the order is Last First (like in your next to last example), then how is the code supposed to determine it needs to be reversed while First Last (also with no comma between the name parts) does not need to be reversed? To the code, they will both look the same... word-space-word.