Formula Vlookup, Find, and Left

[ANE0709]

i am trying to piece together a formula that does a vlookup and returns only the last name. ive gotten close but no dice. Can someone help?

what i have so far: Sheet "Form" G4=LEFT(VLOOKUP(D5,'Pathway Raw Data'!B:I,8,0),4) returns "VAN " and needs to return "Van Meter"

I need to change the 4 to return everything to the left of the comma. Ive tried a few different combos but cant seem to figure it out. Maybe there is a different way of going about this altogether?

 

[6StringJazzer]

This will work in all versions

=LEFT(VLOOKUP(D5,'Pathway Raw Data'!B:I,8,0),FIND(",",VLOOKUP(D5,'Pathway Raw Data'!B:I,8,0))-1)

Since you have 365 you can also do this, which is a little more readable:

=LET(FullName, VLOOKUP(D5,'Pathway Raw Data'!B:I,8,0), LEFT(FullName,FIND(",",FullName)-1))

 

[Joe4]

Try this formula:

=LET(fname,VLOOKUP(D5,'Pathway Raw Data'!B:I,8,0),LEFT(fname,FIND(",",fname)-1))

EDIT: Wow, that is funny, my formula is almost exactly the same as Jeff's!
I haven't used the LET function much, so it took me some time to research it and come up with the formula, and in the meantime Jeff posted it!
(Really, I wasn't copying you Jeff!)

 

[ANE0709]

Thanks guys! the =left(vlookup(),find(vlookup())) jeff suggested was one of the combo's i had attempted but didnt work. I know see my error and jeff's suggestion worked like a charm!