The HASALL function allows you to check whether an array or range contains every one of the values you're looking for. It returns TRUE only if all of the values are found, and FALSE if any of them are missing.
HASALL searches every level of a nested array, so you can use it on arrays that are stored inside a single cell.
Syntax
The HASALL function determines whether array contains all of the values.
The syntax of the HASALL function is:
HASALL(array, values)
| Argument | Description |
|---|---|
| array (required) |
The array or range to search within. Every level of a nested array is searched. |
| values (required) |
The values to check for. HASALL returns TRUE only if all of them are found. |
Note
- HASALL always returns a single TRUE or FALSE, no matter how many values you search for. To get one result per value, use the HAS function instead. To check whether at least one value is present, use the HASANY function.
- HASALL checks whether each value is present, not how many times it appears. Repeating a value in values doesn't require it to appear more than once in array.
- Comparisons are strict about type. The number 1 doesn't match the text "1", and the number 1 doesn't match TRUE. Text comparisons aren't case-sensitive, so "apple" matches "APPLE".
- If array is empty, HASALL returns the #CALC! error.
- If values contains an error, that error is returned.
Examples
Copy the example data and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.
Example 1
Check whether a range contains every value in a set.
| Data | ||||
|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 |
| Formulas | Questions |
|---|---|
| =HASALL(A2:E2,{2,4}) | Are both 2 and 4 in the list? |
| =HASALL(A2:E2,{2,9}) | Are both 2 and 9 in the list? |
| =HASALL(A2:E2,{1,2,3,4,5,6}) | Are all six values in the list? |
The following illustration shows the results.
Example 2
See how HASALL treats repeated values. It checks whether a value is present, not how often it appears.
| Data | |
|---|---|
| 1 | 2 |
| Formulas | Description |
|---|---|
| =HASALL(A2:B2,{2,2,2}) | Returns TRUE. The value 2 is present, which is all that is required |
| =HASALL(A2:B2,{1,2,3}) | Returns FALSE. The value 3 is missing |
The following illustration shows the results.
Example 3
Search inside a nested array. The formula ={{{1,2};{3,4}}} in cell A1 stores a nested array in a single cell.
| Formulas | Description |
|---|---|
| =HASALL(A1,{2,3}) | Returns TRUE. HASALL searches every level, so 2 is found inside {1,2} and 3 inside {3,4} |
| =HASALL(A1,{2,5}) | Returns FALSE. The value 5 does not appear at any level |
The following illustration shows the results.
Example 4
Check that a row is tagged with every required tag. Each cell in column B holds a List of tags, displayed separated by commas.
After you paste the data, select B2:B3 and press Ctrl+J to turn each cell into a List. Until you do, each cell holds a single piece of text, and the formulas below won't find the individual tags.
| Item | Tags |
|---|---|
| Product A | urgent, review, Q1 |
| Product B | urgent, Q1 |
| Formulas | Description |
|---|---|
| =HASALL(B2,{"urgent","review"}) | Returns TRUE. Product A has both tags |
| =HASALL(B3,{"urgent","review"}) | Returns FALSE. Product B is not tagged review |
The following illustration shows the results.
