Is the Last Name from Column D in Column C?

[Nate Lawrence]

In my job, we mail out forms to our customers.
One part of my job is to ensure that the name for the forms matches the shipping name.

If the two names don't match, I highlight them in our manifest so that our team stuffing the envelopes knows that a particular form and envelope are indeed a pair.

To avoid manually examining every single order in this regard, I did create a column after the last column in the worksheet to test whether the name on the form exactly matched the name on the address. (Our customers frequently include their middle initial or name to be printed on their form, but do not include it on their mailing address.)

This did reduce my workload, however even better would be if I could extract the last name from the Shipping Name (Column D) and test whether that extracted last name matches any part of the name for the form (Column C).

That way, the number of orders which I need to manually review decreases from 16 in 20 to perhaps 1 or 2 in 20, our turnaround time gets faster for our customers, my employers don't need to pay me for as much time, and I have more free time outside of work.

As an example, imagine that in C2 you have Nathanael D. Lawrence (for the form) and in D2 you have Nate Lawrence (for shipping).
Basically, I want to extract "Lawrence" from "Nate Lawrence" and test "Nathanael D. Lawrence" to see whether that value exists and return True or False in my formula column.

I did some searching this afternoon and did find a formula to extract the last name from Column D.

=TRIM(RIGHT(SUBSTITUTE(D2," ",REPT(" ",100)),100))

Where I'm failing is being able to take the result of that query (which seems to be working) and search for the identified name in Column C.

Thanks in advance for your help.

 

[Marcelo Branco]

Try

=ISNUMBER(SEARCH(TRIM(RIGHT(SUBSTITUTE(D2," ",REPT(" ",100)),100)),C2))

M.

 

[Peter_SSs]

We may possibly need more examples of what you have and what you expect as results. My reason being that with Marcelo's formula, which seems to be doing exactly what you asked, you could get quite misleading results - see rows 3 & 4 in column E below. Would you really want to show those as a match?

I Have suggested a slight modification in column F which eliminates those two TRUE values, but what about row 5 - what would you want to happen there? They are clearly different people but do have matching names.

Perhaps you could just compare the last name in each column - columnG?

21 09 21.xlsm
	C	D	E	F	G
2	Nathanael D. Lawrence	Nate Lawrence	TRUE	TRUE	TRUE
3	Jim Johnstone	Ken Johns	TRUE	FALSE	FALSE
4	Tim Johnson-King	Ken Johns	TRUE	FALSE	FALSE
5	Taylor Swift	Rod Taylor	TRUE	TRUE	FALSE
Search Name

Cell Formulas
Range	Formula
E2:E5	E2	=ISNUMBER(SEARCH(TRIM(RIGHT(SUBSTITUTE(D2," ",REPT(" ",100)),100)),C2))
F2:F5	F2	=ISNUMBER(SEARCH(" "&TRIM(RIGHT(SUBSTITUTE(D2," ",REPT(" ",100)),100))&" "," "&C2&" "))
G2:G5	G2	=TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",100)),100))=TRIM(RIGHT(SUBSTITUTE(D2," ",REPT(" ",100)),100))