Return true if all values are found in an array

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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,089,902
Messages
5,411,118
Members
403,342
Latest member
faizanhamied

This Week's Hot Topics

Top