Return true if all values are found in an array

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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,508
Messages
5,414,978
Members
403,559
Latest member
Jim Cumberbatch

This Week's Hot Topics

Top