Name in different ways captured

[DHayes]

Good Day,
I am hoping there is someone that can help with a formula. The problem is the data is not captured constantly. So i have a name that gets captured in 3 different ways I need to find the value of this person for a specific month. Please if there is formula that I can use would be appreciated.

[TABLE="class: grid, width: 348"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD="align: left"]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Name[/TD]
[TD="align: left"]Sales[/TD]
[TD="align: left"]Month[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]John James Smith (John)[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]Oct-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]John James Smith[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]Nov-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Smith John James[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]Dec-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]Oct-19[/TD]
[TD="align: right"]Nov-19[/TD]
[TD="align: right"]Dec-19[/TD]
[/TR]
[TR]
[TD="align: left"]John James Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Legacy 436357]

Why is this name captured differently? Excel cannot read your mind sorry. There may be other people with similar first, middle, or last names in different combinations.

I suggest you assign an ID number to that person and use that ID in your formulas.

I hope that helps,
XJ