Hello
I have the following vlookup formula..
=IF(A5="","",VLOOKUP(A5,'Portfolio Overview'!C:D,2,FALSE))
Depending on what's in cell A5, it can return one of five values..
Pipeline: Start Up
Pipeline: Pre-Initiation
In-Flight: Initiation
In-Flight: Delivery
In-Flight: ELS & Close
I would like it to return just the text after the space, instead of the entire cell, so..
Start Up
Pre-Initiation
Initiation
Delivery
ELS & Close
Appreciate any help..
Many thanks
Tom
Is it only those 2 possible pre-texts ? Pipeline: or :In-Flight: ?
If so, try
=IF(A5="","",TRIM(REPLACE(VLOOKUP(A5,'Portfolio Overview'!C:D,2,FALSE),1,10,"")))
To make the formula easy, perhaps first add a helper column ( that can be hidden say col D ) with your formula, and in the result column something like =right(d1,len(D1)-search(":",d1)-1)
Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Many thanks for all your suggestions.. I used TNS's and it works perfectly. I have another similar formula I wonder if you could help me with though?
I need exactly the same value returned, but I'm using an Index Match formula as the column I'm looking up against (F) is to the right of the column with the value I would like returned (D).
=IF(B47="","",INDEX('Portfolio Overview'!D:D,MATCH('Portfolio Gantt'!B47,'Portfolio Overview'!F:F,0)))
Again, the possible values returned depending on cell B47 are..
Pipeline: Start Up
Pipeline: Pre-Initiation
In-Flight: Initiation
In-Flight: Delivery
In-Flight: ELS & Close
And I'm trying to get it to return whatever appears after the space, so..
Start Up
Pre-Initiation
Initiation
Delivery
ELS & Close
Many thanks for your help.
Regards
Tom
Can't you just swap the INDEX/MATCH for the VLOOKUP?
I didn't think I could use the VLOOKUP function when the column of the value I want returned is to the left of the column I'm looking up against?
No, you can't (strictly speaking, although there is a way ... don't ask). That isn't what I meant. Where you have a VLOOKUP formula in the original solution, replace each occurrence of that VLOOK formula withe your INDEX/MATCH formula. The INDEX/MATCH will return a string which can be operated on by the MID function.
Untested:
Formula:Please Login or Register to view this content.
Regards, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks