Return true if all values are found in an array

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
Assuming your array is in A1:A6, and the values in B1:B3, you could use this array formula

Code:
=COUNT(MATCH(B1:B3,A1:A6,0))=COUNT(B1:B3)
 

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
In VBA, do you want it as part of a bigger routine, or do you want a UDF that returns true or false to the worksheet.
 

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
Code:
Public Function MatchAll(ByRef Target As Range, ByRef Values As Range) As Boolean
Dim cell As Range

    MatchAll = True
    For Each cell In Values.Cells
    
        If IsError(Application.Match(cell.Value, Target, 0)) Then
        
            MatchAll = False
            Exit Function
        End If
    Next cell
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,090,217
Messages
5,413,130
Members
403,464
Latest member
TheRepairGuy

This Week's Hot Topics

Top