Substitute an array of values with a single value

[gavcol]

Hi,

Thanks in advance for helping.

I have a formula that I want to streamline a bit (too many nested substitutes)

Is there a way to substitute multiple values for a single value but without nesting a heap of Substitute functions ?
maybe something along the lines of the following :
=SUBSTITUTE(VLOOKUP($D2,DataRange,4,FALSE),{"W","T","O","P","R",D","S"},"Productive")

I use something similar in a sumifs formula and it works very well but not with substitute unfortunately

Cheers,
Gav

 

[FDibbins]

What exactly are you trying to do?

Provide some sample data and show what you expect please
(and what worked?)

 

[gavcol]

What exactly are you trying to do?

Provide some sample data and show what you expect please
(and what worked?)

Hi,

Thanks in advance for helping.

I have a formula that I want to streamline a bit (too many nested substitutes)

Is there a way to substitute multiple values for a single value but without nesting a heap of Substitute functions ?
maybe something along the lines of the following :
=SUBSTITUTE(VLOOKUP($D2,DataRange,4,FALSE),{"W","T","O","P","R",D","S"},"Productive")

I use something similar in a sumifs formula and it works very well but not with substitute unfortunately

Cheers,
Gav

 

[gavcol]

What exactly are you trying to do?

Provide some sample data and show what you expect please
(and what worked?)

Hi Ford (sry, I accidentally reposted the original post and had to rewrite everything again (also before I realised there was an autosave feature) )

What I'm trying to do is make the following working formula a bit leaner.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(VLOOKUP($D2,DataRange,4,FALSE),"W","Productive"),"T","Productive"),"O","Productive"),"R","Productive"),"P","Productive"),"D","Productive"),"S","Productive"),"L","Leave"),"U","UnProductive")

Basically it's a vlookup, returning values from column 4 and substituting the single character results to a more meaningful word.
i.e. :
W,T,O,R,P,D,S = Productive
L = Leave
U = UnProductive

I used to do this with nested IFs but the formula was very long.
I reduced this a fair bit by using the SUBSTITUTE function but it's still seems like I'm doing it the long way.

In another formula (while I realise it's not the same formula and operates very differently ) is an example of using multiple criteria within {}
=SUM(SUMIF([@[Call TypeRC]],{"Construction","AH","DC","MW","PC","PM","QU","UB","WA"},[@[PTDHrsInvcd-1]]))

Is there a similar leaner or more condensed way of handling multiple substitutions than the way I'm doing it above ?

Cheers & Happy Christmas

 

[RoryA]

The short answer is no, not without code. Excel's text manipulation is fairly basic compared to the rest of it.

 

[gavcol]

The short answer is no, not without code. Excel's text manipulation is fairly basic compared to the rest of it.

Ah, Ok.
As Long as I'm doing it in the best way possible.

Cheers,
Gav

 

[gavcol]

Ah, Ok.
As Long as I'm doing it in the best way possible.

Cheers,
Gav

Ok, figured out another way of doing it to condense the formula length if anyone else comes across this post in the future.
I doubt it's all that leaner on the cpu because it's now performing the lookup twice (twice is still better than all the nested IFs I had in the original formula) but at least the formula itself is easier for others (like my manager) to read and understand what it's doing.

I created a Name 'LookupDataRange' for the vlookup then performed an isnumber/find validation for the results that are Productive
I left the Substitutes in the 2nd part of the formula because it's fairly short and highlights there's more than one way to skin a cat

=IF(OR(ISNUMBER(FIND({"W","T","O","P","R","D","S"},LookupDataRange))),"Productive",SUBSTITUTE(SUBSTITUTE(LookupDataRange,"L","Leave"),"U","UnProductive"))

Cheers and Happy New Year

 

[FDibbins]

Another way would be to create a small table with your codes and meanings, then use another vlookup (around your initial formula) that will pull the code meaning in for you. Something like...

=vlookup(VLOOKUP($D2,DataRang e,4,FALSE),$Y$1:$Z$3,2,0)

 

[gavcol]

Another way would be to create a small table with your codes and meanings, then use another vlookup (around your initial formula) that will pull the code meaning in for you. Something like...

=vlookup(VLOOKUP($D2,DataRange,4,FALSE),$Y$1:$Z$3,2,0)

aaah, good. I should have thought of that one. Even more condensed.
And the cat gets skinned again

 

[Rick Rothstein]

aaah, good. I should have thought of that one. Even more condensed.
And the cat gets skinned again

If you did not want to create the extra table, I think this formula might work directly...

=IF(ISNUMBER(FIND(VLOOKUP($D2,DataRange,4,FALSE),"WTOPRDS")),"Productive","Unproductive")