find a name in a list instead of absolute reference

[smls]

Hi all,

I'M back for help again. I am having trouble working with an expense report file. People manually input their expenses and aren't always good about keeping the naming conventions correct to help me sort them (I can't add anything to the system to force them into giving me good data or I would). each description has at minimum their last name. I also have a listing of all Employee names. Here is what I am trying to do.

In the workbook I have the list of names in COlumn H and the field with the last name somewhere in column A
I currently have a formula

=MID(A2,FIND(H2,A2),15)

This only works if I link the formula to a cell in H with the correct employee description in A. If I don't I will get an error.

Is there a way, VBA or otherwise, to get the formula to check through the list and find the correct last name to pull out?

Thanks in advance,
smls

[tim201110]

sure, with your file.

[José Augusto]

Hi
Try using Data Validation
Suppose H2:H30 as complete Names
Use in A2 Data validation customized with this formula

=COUNTIF($H$2:$H$30,"*"&A2&"*")=1  

You can copy special data validation for other cells f column A

[smls]

Not quite sure I follow, here's a test file that kind of illustrates it.

[José Augusto]

Hi
Please clarify. Your column H has only Last names? Column A has the input data? In the example Column B has the names (First, Last) or (Last Two) or (something and Last)?

[smls]

Column A has the input data from the system with the mixing of names and line details. Column B simply has the mid formula I wanted to make work but couldn't. H is only last names in the test file. In the real file it has both but I can easily make it last name only if that's needed.

[José Augusto]

Hi
I assum that H2:H60 has Last names, I2:I60 has Complete names
Use in D2 the following formula and drag down

=INDEX($I$1:$I$60,SUMPRODUCT(ROW($H$2:$H$60)*COUNTIF(A2,"*"&$H$2:$H$60&"*")))

In J2 I add a new formula to see if last names is unique inside complete names. If not then you can adapt Last name as I do with Smith.

See the file for clarification.

[smls]

I think you misunderstand the objective.

I am trying to get the last name, or full name if possible, appear in column B. That would need me to find it inside of the String in A and pull it out. This would need to reference the list in H. My mid formula was getting me what I wanted in that one instance, it just doesn't work to give me what I want for the entire list.

So moving through it I would want column B to read Zimmer, Smith, Jordan, Smith, Brown. Or some variant of the last name appearing in the column.

Column B would then be used to determine which people each expense was allocated to.

Your formula appears to only tell me if the name exists. As they are already in the system it's past the validation to be paid part of the process. This is now an allocation to person and department.

Does that make sense?

[smls]

Or did I misunderstand the formula in the sheet you re-posted? Also, I couldn't get it to execute False or "xx".

[José Augusto]

Hi
It works fine for me. I download the file and it gives the complete name in D2:D7 and Ok in J2:J6
see the image
Capturar.JPG
and the same file

[smls]

I must have looked at something else before, or be going insane, either way I was wrong, this file works. I see what you did, just had to adjust the Complete names and my brain started working. Much appreciated. Great solution to it. I think I'm following it. +rep :D

[José Augusto]

I'm glad to have helped.
Thanks.