IF value equals any of the values in a range of cells

[Erick]

How do i write an IF macro statement so that compares a value to a range of values contained in a range of cells in a worksheet. If the value is equal to any of the values in the range of cells, then it returns true.

 

[phantom1975]

The following macro looks at each cell in A1:A10 and compares it to the range of cells in D1:D4. If there is a match, then "TRUE" is entered in the corresponding cell in column B:

Dim ThisCell1 As Range
Dim ThisCell2 As Range
    For Each ThisCell1 In Range("A1:A10")
    'This is the range of cells to check
        For Each ThisCell2 In Range("D1:D4")
        'This is the range of cells to compare
            If ThisCell1.Value = ThisCell2.Value Then
                ThisCell1.Offset(, 1).Value = "TRUE"
                Exit For
                End If
            Next ThisCell2
        Next ThisCell1

 

[brucesw]

You could use a formula too.

The =IF(ISNA(MATCH(G10,D3:D18,0)),"N","Y")

Where G10 is the reference you are testing and D3:D18 is the range you are searching in.

 

[Yogi Anand]

Hi Erick:

If my value to compare is in cell A1, and the range of values to compare with is A2:A6 then using a Macro, one of the ways I can do this is by

Sub yComparebyMatch()
    On Error Resume Next
    result = IsNumeric(WorksheetFunction.Match(Range("A1"), Range("A2:A6"), 0))
    If result = True Then MsgBox "It is True"
End Sub

 

[VTHokie11]

What if the range is dynamic but always starts in cell A3 and ends once it reaches "Grand Total." (Looking at a pivot table that I want to adjust based on cell contents of another cell)

 

[Telejazzer]

You could use a formula too.

The =IF(ISNA(MATCH(G10,D3:D18,0)),"N","Y")

Where G10 is the reference you are testing and D3:D18 is the range you are searching in.

Hi, Thanks for this tip however this only works on a typed (in cell) value? Would you be able to recommend checking the end value of a formula? See the value that I'm after is the first 7 digits of a cell value e.g. =LEFT(B1,7) which is the value of cell A1. When I use A1 as the reference it doesn't work however if I type the value in A1 it works. Is there a way around it?

 

[Zorod]

If a match is found, instead of entering "Y" how can I have it choose the text in the cell just to the right of the matched item.

 

[writelynn]

You could use a formula too.

The =IF(ISNA(MATCH(G10,D3:D18,0)),"N","Y")

Where G10 is the reference you are testing and D3:D18 is the range you are searching in.

Excellent Formula. There were much more complicated formulas all over the web. This simple one worked perfectly. Thanks.

 

[VultureofNapa]

Hi Erick:

If my value to compare is in cell A1, and the range of values to compare with is A2:A6 then using a Macro, one of the ways I can do this is by

Sub yComparebyMatch()
    On Error Resume Next
    result = IsNumeric(WorksheetFunction.Match(Range("A1"), Range("A2:A6"), 0))
    If result = True Then MsgBox "It is True"
End Sub

How about this:

Say I have a 26,000 line G/L and need to find several values that equal X (x in my case is 129.77). I'm pretty sure two or three separate entries were made that add up to this amount. What macro or equation could be used to figure this out? It's got me stumped!!! Thanks!

 

[Wobzy]

Hey guys,

Sorry to bump an old thread.

This is fairly close to what im trying to achieve.


Sheet name; list (plain text, data lists)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Mister X[/TD]
[TD]Contract A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mister Y[/TD]
[TD]Contract C[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mister L[/TD]
[TD]Contract B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mister T[/TD]
[TD]Contract A[/TD]
[/TR]
</tbody>[/TABLE]

Sheet name; work (A data validation, B formula)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=LIST!$A:$A [/TD]
[TD]<strike></strike>=IF(A:A=LIST!A:A,LIST!B:B)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mister T[/TD]
[TD]Contract A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mister L[/TD]
[TD]Contract B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, in cell A1 I choose a name from data validation drop down, linked to LIST!A:A
The adjacent cell B1 needs a formula to tell me what the corresponding LIST!B:B entry is.

I know I'm looking for something along the lines of MATCH or LOOKUP.

Help is appreciated.