• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combine Vlookup function & right, left & search "," to swap surname and first name

Hi, I hope you're are well.

I would love some assistance to combine vlookup as my first criteria then swap the format from surname, first name to first name surname

vlookup A to get C, if blank use column E - the desired outcome is in Column E

This part I think I've figured out i.e. =IFERROR(RIGHT(C4,LEN(C4)-SEARCH(",",C4)-1)&" "&LEFT(C4,SEARCH(",",C4)-1),D4) with some help from google.

If you are able to assist, I would greatly appreciate it.

Any questions please don't hesitate to ask. take care, Kelli
 

Attachments

  • Chandoo - Manager Name.xlsx
    20.2 KB · Views: 9
Try,

In Column E, E4 formula copied down :

=IFERROR(MID(C4&" "&C4,FIND(" ",C4)+1,LEN(C4)-1),D4)

Regards
 
Last edited:
Try,

In Column E, E4 formula copied down :

=IFERROR(MID(C4&" "&C4,FIND(" ",C4)+1,LEN(C4)-1),D4)

Regards
Hi Bosco,
In column J I've added my vlookup, but I need to include the formula you provided to convert to First Name & then Surname but if blank, use column D.

I hope this makes sense, I really struggle communicating this:rolleyes: I hope all is well in your part of the world!

Kind regards, Kelli
 

Attachments

  • Chandoo - Manager Name.xlsx
    20.2 KB · Views: 3
In J4, formula copied down :

=VLOOKUP($H4,$A:$D,IF($C4<>"",3,4),0)

Regards
Hi Bosco,
The names weren't input into column C correctly. I still need the names to be First Name Surname without any comma's.
Sorry it's probably time for bed.
 

Attachments

  • Chandoo - Manager Name.xlsx
    24.2 KB · Views: 11
Seems we have a new formula kid on the block :awesome:.

Since I'm experimenting with dynamic arrays functions, wanted to give it a go...
With DAF onboard the formula can do with less arguments, though I had to use an extra IF inside the XLOOKUP to deal with the "no names" in column C.
=TRIM(MID(SUBSTITUTE(REPT(XLOOKUP(H4:H27,A4:A27,IF(C4:C27<>0,C4:C27,D4:D27))&",",2),",",REPT(" ",40)),40,80))

Do not thing that's optimal though.
 
Please try at J4
=VLOOKUP("?*",INDEX(TRIM(MID(SUBSTITUTE(REPT(VLOOKUP(H4,$A$4:$D$27,{3;4},)&",",2),",",REPT(" ",40)),40,80)),),1,0)
Hi
Please try at J4
=VLOOKUP("?*",INDEX(TRIM(MID(SUBSTITUTE(REPT(VLOOKUP(H4,$A$4:$D$27,{3;4},)&",",2),",",REPT(" ",40)),40,80)),),1,0)
Excel Wizard, this is working perfectly. What would I need to change for it to be the 1st inital of the first name? All the rest is fine
thanks and welcome to Chandoo!
Kelli
 
Back
Top