Search range based on input from a cell and then contents of list cell immediately to the left in an array

  • Hi, I have a list of text values I need to search a range in a workseet. The range is just one column. I need to search that column "E" and then return the value in the same row but from column C. I tried using INDEX ARRAY but that didn't work.

    Hoping VBA could be a solution. It has been a while since I have used VBA so I am a little rusty.


  • Where is the list of values to be searched? Where do you want to return the values from column C?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • As "Mumps" mentioned, don't be shy by giving us needed information in a concise manner.

    You have to remember that you have the workbook in question in front of you while we're just taking a stab at what we think it looks like.

    For lack of pertinent info, either of these or a combination maybe.

    1. Sub Maybe_A()
    2. Dim txtArr, i As Long
    3. txtArr = Array("Text1", "Text2", "Text3") '<---- List of values. Expand as required
    4. For i = LBound(txtArr) To UBound(txtArr)
    5. MsgBox Columns(5).Find(txtArr(i), , , 1).Offset(, -2).Value
    6. Next i
    7. End Sub

    1. Sub Maybe_B()
    2. Dim c As Range
    3. For Each c In Range("H2:H" & Cells(Rows.Count, 8).End(xlUp).Row) '<---- List of values to be found
    4. Columns(5).Find(c, , , 1).Offset(, 2).Value = Columns(5).Find(c, , , 1).Offset(, -2).Value
    5. Next c
    6. End Sub