Need formula to search a column for names where not always listed the exact same as lookup

[g1terra]

So what I am looking for is a formula that will search a column for a name where the lookup value could have an additional name in it (in addition to first and last). The lookup location is not always the exact same. I am attaching a sheet with notes to help see what I am trying to do. I could not get VLOOKUP to work or index match seeing this name is not always the same. Maybe including SEARCH with it will help, I'm stuck?

Any help is much appreciated!!

[Richard Buttrey]

Where do the names in the Test sheet come from?
If they were to contain the numeric references we see in the Export csv sheet then this would be much easier. And what do you mean by the lookup 'location'?

[thatspreadsheetlife]

Similar to what Richard said, it would be much for effective to try to match the names from the two sheets using the person's ID that is found on worksheet "Exportcsv".

Perhaps you could create a table for the possible names that would appear on the first worksheet (such as "CLAUDIA M AVALOS AYBAR") and the person's ID. Then you could use a vlookup to get the ID number for the name that appears on worksheet TEST, and from there it would be fairly easy to use a text function to extract ID numbers on Exportcsv to do your comparison.

[g1terra]

That was the first think I thought of. The issue its coming for 2 different systems. One is a payroll ID and another a different ref ID. So its currently not possible to do this...

[JeteMc]

This proposal works for the sample:
1. Column U on the exportcsv sheet removes the numeric code and brackets using: =LEFT(H2,LEN(H2)-9)
2. The formula to find the remarks is:

Please Login or Register  to view this content.

Let us know if you have any questions.

[menem]

Please try this formula.

J11

Please Login or Register  to view this content.

Press Ctrl-Shift-Enter

** This formula is search from left word and right word from name field (A11)

Regards.