Vlookup that trim/mid the result in a single formula

[mkyb14]

I'm trying to use a vlookup to reference an account number, and return the managers name in another sheet. Problem is the managers name is backwards and I've been using Trim/Mid to fix this. How can I incorporate a vlookup of a value, return a name but have it flipped and trimmed etc? Normally I'd create another sheet and manually do this, but I'd like to try and not create a bunch of other sheets for single tasks.

Gemini = Raw account list. S column listed mangers names by (LAST, FIRST), column D = account number

Master List = where i'm trying to create a master file of specific accounts by Account number.
Column A = Account number
Column G = Vlookup (column A) against (Gemini!D:G), then return managers name (First Last)

iferror(trim(mid(Gemini!S2&" "&Gemini!S2,FIND(",",Gemini!S2)+1,LEN(Gemini!S2))),"To Be Deleted if no match"))

 

[Joyner]

Hello, I'm not quite following your formula above or what you want to do, but if you want to flip the names, try:

=TRIM(RIGHT(SUBSTITUTE(Gemini!S2,",",REPT(" ",255)),255))&" "&TRIM(LEFT(SUBSTITUTE(Gemini!S2,",",REPT(" ",255)),255))

Not sure, but if you want to do it in a lookup formula maybe something like:

=IFERROR(TRIM(RIGHT(SUBSTITUTE(Lookupformula for name,",",REPT(" ",255)),255))&" "&TRIM(LEFT(SUBSTITUTE(Lookupformula for name,",",REPT(" ",255)),255)),"To Be Deleted...")

 

[mkyb14]

Hello, I'm not quite following your formula above or what you want to do, but if you want to flip the names, try:

=TRIM(RIGHT(SUBSTITUTE(Gemini!S2,",",REPT(" ",255)),255))&" "&TRIM(LEFT(SUBSTITUTE(Gemini!S2,",",REPT(" ",255)),255))

Not sure, but if you want to do it in a lookup formula maybe something like:

=IFERROR(TRIM(RIGHT(SUBSTITUTE(Lookupformula for name,",",REPT(" ",255)),255))&" "&TRIM(LEFT(SUBSTITUTE(Lookupformula for name,",",REPT(" ",255)),255)),"To Be Deleted...")

So I have the flip down on the first and last names with my original code (unless I missed something). What I'm trying to do is first have the vlookup match the item and do the flip and value it pulls back. So in one motion, vlookup/match the result/flip the result to display the name correctly.

 

[Peter_SSs]

Gemini = Raw account list. S column listed mangers names by (LAST, FIRST), column D = account number

Master List = where i'm trying to create a master file of specific accounts by Account number.
Column A = Account number
Column G = Vlookup (column A) against (Gemini!D:G), then return managers name (First Last)

The requirement is not clear to me.
In Gemini, it seems the Account numbers are in column D and the Names (Last, First) are in column S.
What is in, I presume, column G that will be returned by the red part above?

 

[mkyb14]

The requirement is not clear to me.
In Gemini, it seems the Account numbers are in column D and the Names (Last, First) are in column S.
What is in, I presume, column G that will be returned by the red part above?

ok I mocked up a sample in google sheets since it's CPNI data.
https://docs.google.com/spreadsheets/d/1dUcZFDLRfXpYAOwEkGqsKD6t7SEmA7SejYnTbYQF76g/edit?usp=sharing

Hopefully the sheet overview and sample data can show what I'm trying to do. 2 data sources, flowing into one master sheet that I can import what I need from each in the order, format etc. From there I'll I'd like to do is have to monthly update the SFDC / Gemini sheets with the most recent data and the master overview sheet will update everything. This is then a data source for google data studio where I will have my dashboard present this in graphical form.

 

[Joyner]

Hello, I can't download that file now, but looking at it, it looks like you could do a formula like I show, inserting the VLOOKUP formula.

If this is something you are doing on a regular basis for this data, it looks like a perfect job for Power Query. If you are not familiar with that, there are several good videos that show how it would work.

 

[mkyb14]

Hello, I can't download that file now, but looking at it, it looks like you could do a formula like I show, inserting the VLOOKUP formula.

If this is something you are doing on a regular basis for this data, it looks like a perfect job for Power Query. If you are not familiar with that, there are several good videos that show how it would work.

@Joyner - looks like that worked! thank you for helping me figure that out!

 

[Joyner]

You are welcome

 

[Peter_SSs]

If you are interested, here is a considerably shorter one that I think should do the same job for you.

=IFERROR(TRIM(MID(SUBSTITUTE(REPT(" "&VLOOKUP(A2,Gemini!A1:F5,3,0),2),", ",REPT(" ",100)),100,100)),"To Be Deleted...")

 

[Joyner]

Thanks Peter, that is slick. I seem to recall something like that, but now after walking through it I can add it to may bag of tricks.