Here is my approach to the subject:

Private Sub Textbox1_Change()

Dim i As Long Dim b As long Dim arrList As Variant

Me.ListBox1.Clear If TMP.Range("A" & TMP.Rows.Count).End(xlUp).Row > 1 And Trim(Me.TextBox1.Value) <> vbNullString Then arrList = TMP.Range("A1:A" & TMP.Range("A" & TMP.Rows.Count).End(xlUp).Row).Value2 For i = LBound(arrList) To UBound(arrList) If InStr(1, arrList(i, 1), Trim(Me.TextBox1.Value), vbTextCompare) Then Me.ListBox1.AddItem listBox1.List(b, 0) = TMP.Cells(i, 1).Value listBox1.List(b, 1) = TMP.Cells(i, 2).Value listBox1.List(b, 2) = TMP.Cells(i, 3).Value listBox1.List(b, 3) = TMP.Cells(i, 4).Value b=b+1 End If Next i End If If Me.ListBox1.ListCount = 1 Then Me.ListBox1.Selected(0) = True

End Sub

I have checked and it works perfectly fine. In this case You have to add to the list values from cells on row i, and since i specifies a row it is easy to link it to cell value.