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]

Happy to help

 

[gavcol]

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")

Oh, I see. Thats clever.
You're right, I am hesitant to use the extra table and prefer to keep as much in the one formula as possible.
Although, that formula only has the 2 results for Productive & unproductive but doesn't accommodate the "L" Leave result.