none
formula and vba code to lookup cells using multiple criteria RRS feed

  • Question

  • I need formula and vba code to do the following:

    • if a string in cell A is equal to string in cell D and a string in cell C matches one of the items listed in column E (which is a range), return string listed in cell F.

    A=D, and C = E(range with one cell matching string in cell C) result copy value in cell F.

    Hope i did justice, unable to upload picture its easy to explain in a table.

    Thank you!



    Pete


    • Edited by Pete198 Saturday, January 7, 2017 4:26 PM
    Saturday, January 7, 2017 12:11 AM

Answers

  • F2:  =IFERROR(IF(A2=D2,IF(MATCH(C2,E:E,0),TRUE())),FALSE())
    • Marked as answer by Pete198 Saturday, January 7, 2017 6:08 PM
    Saturday, January 7, 2017 5:54 PM
  • Hi Pete198,

    VBA Code:

    Sub demo()
    Dim i, j As Long
    Dim sh As Worksheet
    Set sh = Sheets("Sheet1")
     Dim LastRow As Long
     Dim str As String
    LastRow = sh.Range("A1").SpecialCells(xlCellTypeLastCell).Row
    
    For i = 2 To LastRow
    If sh.Cells(i, 1).Value = sh.Cells(i, 4).Value Then
         str = sh.Cells(i, 3).Value
            For j = 2 To LastRow
                If str = sh.Cells(j, 5).Value Then
                    sh.Cells(i, 6).Value = True
                    Exit For
                    Else
                    sh.Cells(i, 6).Value = False
                End If
            Next j
    End If
    Next i
    End Sub
    

    Ouput:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Pete198 Monday, January 9, 2017 2:43 AM
    Monday, January 9, 2017 2:25 AM
    Moderator

All replies

  • =IF(A=D,IF(ISNUMBER(MATCH(C,E,0)),F,""),"")

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, January 7, 2017 11:14 AM
  • Hi i tried this but it didn't show the expected result, maybe i did not explain it correctly. i was able to copy picture from excel, please see below:

    if A2=D2, and C2 matches any string in range column E. The result is F2. In this case it will result in true because ABCDE in column C2 is listed in E3 which is a range.

    Thank you!

     



    Pete


    • Edited by Pete198 Saturday, January 7, 2017 4:42 PM
    Saturday, January 7, 2017 4:41 PM
  • F2:  =IFERROR(IF(A2=D2,IF(MATCH(C2,E:E,0),TRUE())),FALSE())
    • Marked as answer by Pete198 Saturday, January 7, 2017 6:08 PM
    Saturday, January 7, 2017 5:54 PM
  • Thank you! This works like a charm. Appreciate both of you looking into it. It would be great to have a vba code to do the same.


    Pete


    • Edited by Pete198 Saturday, January 7, 2017 6:10 PM
    Saturday, January 7, 2017 6:10 PM
  • It would be great to have a vba code to do the same.

    Why? It would not be faster, the effort to implement is much higher... there is no benefit.

    Andreas.

    Sunday, January 8, 2017 12:40 PM
  • Hi Pete198,

    VBA Code:

    Sub demo()
    Dim i, j As Long
    Dim sh As Worksheet
    Set sh = Sheets("Sheet1")
     Dim LastRow As Long
     Dim str As String
    LastRow = sh.Range("A1").SpecialCells(xlCellTypeLastCell).Row
    
    For i = 2 To LastRow
    If sh.Cells(i, 1).Value = sh.Cells(i, 4).Value Then
         str = sh.Cells(i, 3).Value
            For j = 2 To LastRow
                If str = sh.Cells(j, 5).Value Then
                    sh.Cells(i, 6).Value = True
                    Exit For
                    Else
                    sh.Cells(i, 6).Value = False
                End If
            Next j
    End If
    Next i
    End Sub
    

    Ouput:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Pete198 Monday, January 9, 2017 2:43 AM
    Monday, January 9, 2017 2:25 AM
    Moderator