I'm running an excel formula and having issues with a #Spill error. The idea is that I have a column of cells that contain a ton of different numbers. I have another column with a bunch of String values that contain numbers as well.
For example,
| Col A | Col B |
|---|---|
| 1 | String.10 |
| 2 | String.1 |
| 3 | String.3 |
| 4 | String.6 |
The output, after running the formula, should return records of: String.1 String.3
as 1 and 3 are contained in a cell.
The formula:
=IF(ISNUMBER(SEARCH($A$2:$A$10,B2)), "Yes", "No")
The idea is that I have a static range of cells to compare to and a way longer list of String.'numbers'. Why would this function result in a spill error?
CodePudding user response：
=BYROW($B$2:$B$9,LAMBDA(B,ISNUMBER(XMATCH(--(INDEX(TEXTSPLIT(B,".",,),2)),$A$2:$A$9))))
This will spill results (TRUE/FALSE) for all values.
It splits the text in column B and shows the string after the .. It's then converted from text to number by using --() and that value is matched with the values in column A. If it matches it returns the row number, so ISNUMBER is TRUE.
If no match is found, it throws an error, which is not a number, so ISNUMBER is FALSE.
CodePudding user response：
Although this seems to be a trivial task, there are some pitfalls that need to be taken into account. For example, the problem that the search for "1" will be TRUE for both String.10 and String.1.
One approach to solving this issue in one formula could be as follows, combining, in particular, the following functions: TEXTJOIN(), BYROW(), and FILTER(). I also use some other functions that come in handy to find the exact string, etc.
Assuming that the data is stored in the range A2:B5, you can enter the following formula in C2, for example:
=TEXTJOIN(";",TRUE,
BYROW(A2:A5,LAMBDA(rowN,
FILTER($B$2:$B$5,
EXACT(rowN,RIGHT($B$2:$B$5,LEN($B$2:$B$5)-SEARCH(".",$B$2:$B$5)))=TRUE,""))))
The output of this formula looks as follows: String.1; String.3.
The TEXTJOIN() function combines the output of the BYROW() function separated by e.g, ";". In the BYROW() function, you first specify the array to which you want to apply the function specified in the LAMBDA()statement. In this context, rowN is then in the following simply used as the name for this particular array. The FILTER() function is used to filter data from a specified range that meets a certain criterion. The criterion in this context is that the number (specified in column A) matches the numeric part of the string in column B. To extract only the numeric part of the string, the number after the "." is extracted by combining the RIGHT(), LEN(), and SEARCH() functions. Subsequently, it is important to use the EXACT() function to ensure that when searching for "1", only this particular row is recognized as TRUE and not also the number "10", which also contains a 1.
Variation of the specification to get the output in a different format:
If you do not want the strings combined, you can simply delete the TEXTJOIN() function, which will return the corresponding string or an empty cell:
=BYROW(A2:A5,LAMBDA(rowN,FILTER($B$2:$B$5,EXACT(rowN,RIGHT($B$2:$B$5,LEN($B$2:$B$5)-SEARCH(".",$B$2:$B$5)))=TRUE,"")))
If you want to return "Yes" or "No", you can put the formula into an additional IF() statement as follows:
=IF(BYROW(A2:A5,LAMBDA(rowN,FILTER($B$2:$B$5,EXACT(rowN,RIGHT($B$2:$B$5,LEN($B$2:$B$5)-SEARCH(".",$B$2:$B$5)))=TRUE,"")))<>"","Yes","No")
In addition, I give some flavor for the spill error. This occurs because you are looking for a specific string in an array of values. Therefore, the output returns "Yes" and "No" for each row. In your particular case (the formula you presented above), an array containing 9 times "Yes" or "No" will be returned. Thus, if you then copy the formula down, you will get the spill error because you have stored something in the row and it is not possible to spill the 9 values down.