Excel – Formula to Compare Single Value with Multiple Values

microsoft excelvbaworksheet-function

I have a value in Column A, which I want to compare with multiple values in column B, and depending on that value, put the answer in column C.

For example, using the table below, it searching in column B for values which are less than or equal to 12 and put the answer in same order in column C.

Column A     Column B            Column C
12           0,12,13,14          Yes, Yes, No, No    
101          101,102,103,104     Yes, No, No, No

How can I do this in Excel?

Best Answer

This does exactly what you want.

Option Explicit

Sub DoTheThing()

Dim row As Integer
row = 1 ' WHAT IS THE STARTING ROW

Do While (Range("A" & row).Value <> "")

    Dim vals() As String
    vals = Split(Range("B" & row).Value, ",")

    Dim lookUpValue As String
    lookUpValue = Range("A" & row).Value

    Dim result As String
    result = ""

    Dim i As Integer

    For i = 0 To UBound(vals)

        If CSng(lookUpValue) >= CSng(vals(i)) Then
            result = result & "Yes, "
        Else
            result = result & "No, "
        End If

    Next i

    result = Trim(result)
    result = Left(result, Len(result) - 1)

    Range("C" & row).Value = result

    row = row + 1
Loop

End Sub

My worksheet looked like

enter image description here

And after I run the VBa

enter image description here

Excel kept formatting the columns as number. It must remain as Text!

Related Question