Return true if all values are found in an array

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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,095,338
Messages
5,443,903
Members
405,256
Latest member
ukboyme

This Week's Hot Topics

Top