Lookup against two columns to return value from another column on same sheet

[batchjb69]

Lookup1.xlsx

Hi

I have a data download from a system that is not very well designed and I cannot change (see attached).

What I want to do is lookup to see if the value in column A of the output sheet is present in the column A or B of the input sheet if it is I would like it to return the value in column C to Colum B of the output sheet if not return "not present" in column B of the output sheet.

JB

[berlan]

Hi batchjb69,

here is one option for you:

Sub Main()

    Dim wsIn As Worksheet: Set wsIn = ThisWorkbook.Sheets("Input")
    Dim wsOut As Worksheet: Set wsOut = ThisWorkbook.Sheets("Output")
    Dim rngIn As Range, rngOut As Range, rngFind As Range, c As Range

    'define input range
    With wsIn.Columns("A:B")
        Set rngIn = .Range(.Cells(1), .Find("*", , , , xlByRows, xlPrevious)).Resize(, 3)
    End With

    'define output range
    Set rngOut = wsOut.Range("A2", wsOut.Range("A" & Rows.Count).End(xlUp))

    'loop through output lookup range
    For Each c In rngOut
        Set rngFind = rngIn.Find(c.Value, , xlValues, xlWhole)
        If Not rngFind Is Nothing Then
            c(1, 2).Value = wsIn.Cells(rngFind.Row, rngIn(1, rngIn.Columns.Count).Column).Value
            Set rngFind = Nothing
        Else
            c(1, 2).Value = "not present"
        End If
    Next c

End Sub

Hope it helps,

berlan

[batchjb69]

Lookup1.xlsm

Many thanks saved me a load of work

What about if the value = "not present" it looks up to a third sheet (manual list) and again would return the value in column B. If not in column B it remains "not present"

[FDibbins]

No need for VBA for this. Put this in B2 and copy down...
=IFERROR(IFERROR(VLOOKUP(A2,Input!$A$2:$C$57,3,0),VLOOKUP(A2,Input!$B$2:$C$57,2,0)),"Not Present")

[berlan]

You're welcome, and thanks for the kind feedback

To also go through a manual list, if the value is not present in the input list, try if this works for you (see attached):

Sub Main()

    Dim rngIn As Range, rngOut As Range, rngManual As Range
    Dim rngFind As Range, c As Range

    'define input range
    With ThisWorkbook.Sheets("Input").Columns("A:B")
        Set rngIn = .Range(.Cells(1), .Find("*", , , , xlByRows, xlPrevious)).Resize(, 3)
    End With

    'define output range
    With ThisWorkbook.Sheets("Output")
        Set rngOut = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With

    'define manual list
    With ThisWorkbook.Sheets("Mannual list")
        Set rngManual = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With

    'loop through cells in output range
    For Each c In rngOut

        'lookup against input range
        Set rngFind = rngIn.Find(c.Value, , xlValues, xlWhole)

        'if found in input range, use that
        If Not rngFind Is Nothing Then
            c(1, 2).Value = Intersect(rngFind.EntireRow, rngIn.Columns(rngIn.Columns.Count)).Value
            Set rngFind = Nothing

            'if not found, then go through the manual list, else "not present"
        Else
            Set rngFind = rngManual.Find(c.Value, , xlValues, xlWhole)
            If Not rngFind Is Nothing Then
                c(1, 2).Value = rngFind(1, 2).Value
                Set rngFind = Nothing
            Else
                c(1, 2).Value = "not present"
            End If
        End If

    Next c

    'clean up
    Set rngIn = Nothing
    Set rngOut = Nothing
    Set rngManual = Nothing

End Sub

Best,
berlan

[FDibbins]

Berlan, thanks for the feedback But I think you clicked the negative rep lol...no biggie though

[berlan]

FDibbins, apologies! Realised it when I had pressed the button and it gave me a different response. Was meant to be positive rep added -- nice and simple solution to the problem -- as your other contributions on the forum!

[FDibbins]

Its the thought that counts, thanks for the kind words

[FDibbins]

Again, thanks for the rep points, that should make things square