DeanP
12-12-2018, 06:43 AM
I am using an array formula to find all the rows with a partial text match to a wildcard.
Is it possible:
(a) to use more than 1 wildcard in the formula;
(b) if a certain value is found, return the value of the adjacent cell.
The additional wildcards I want to use are: Assessment, SPS, Volume and Shared
If value "50" is found return value in adjacent cell.
This is my formula:
{=IFERROR(INDEX('Opex YTD'!$A$2:$A$995,SMALL(IF(ISNUMBER(SEARCH("*occupa*",'Opex YTD'!$D$2:$D$995)),ROW('Opex YTD'!$A$2:$A$995)-ROW($A$1)+1),ROWS($J$9:J52))),"End of list")}
Is it possible:
(a) to use more than 1 wildcard in the formula;
(b) if a certain value is found, return the value of the adjacent cell.
The additional wildcards I want to use are: Assessment, SPS, Volume and Shared
If value "50" is found return value in adjacent cell.
This is my formula:
{=IFERROR(INDEX('Opex YTD'!$A$2:$A$995,SMALL(IF(ISNUMBER(SEARCH("*occupa*",'Opex YTD'!$D$2:$D$995)),ROW('Opex YTD'!$A$2:$A$995)-ROW($A$1)+1),ROWS($J$9:J52))),"End of list")}