VBA or formula to run a look up based on a cell value

[DMO123]

Hi All,

i have been looking at many different ways on how to do this, its more than likely very simple but i just cannot figure it out.

i want to look up a value in another sheet based on another cells value. i can get the formula to work on every cell but i just want it to run for the "No" entries.

in column "I" (starting at "I2") there is "Yes & "No" entries, if there is a "No" entry then the formula =IF(ISNA(VLOOKUP([@[Calling Number]],'Outgoing'!G:G,7,FALSE)), "No", "Yes") should run in column "J" to look up a number in another sheet. if it is "Yes" in column "I" then column "J" can be blank.

if it can be written as a VBA how do i include if the result from the lookup is "Yes" move to column "K" and display the value in column "C" of the lookup sheet (where the matching value was found) and then move to column "L" and display the row number the matching value was found on.

i hope this makes sense, apologies if its super simple and i'm missing something obvious.

thanks all!

 

[DanteAmor]

Try this.

Help me with the following information:
- Replace "Origin" with the name of the source sheet with the data.
- Replace "A" with the column where the "Calling Number" is located.
- Replace "G" with the column of the "Outgoing" sheet where the search is performed.

Sub run_lookup()
  Dim sh1 As Worksheet, sh2 As Worksheet, i As Long, f As Range
  Set sh1 = Sheets("[B][COLOR=#0000ff]Origin[/COLOR][/B]")
  Set sh2 = Sheets("Outgoing")
  For i = 2 To sh1.Range("[B][COLOR=#FF0000]A[/COLOR][/B]" & Rows.Count).End(xlUp).Row
    If sh1.Cells(i, "I").Value = "No" Then
      Set f = sh2.Range("[B][COLOR=#008000]G[/COLOR][/B]:[B][COLOR=#008000]G[/COLOR][/B]").Find(sh1.Cells(i, "[B][COLOR=#FF0000]A[/COLOR][/B]"), , xlValues, xlWhole)
      If Not f Is Nothing Then
        sh1.Cells(i, "J").Value = "Yes"
        sh1.Cells(i, "K").Value = sh2.Cells(f.Row, "C").Value
        sh1.Cells(i, "L").Value = f.Row
      Else
        sh1.Cells(i, "J").Value = "No"
      End If
    End If
  Next
  MsgBox "End"
End Sub

Let me know any questions.

 

[DMO123]

@DanteAmor thank you so much works perfectly.

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.