• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup From 2 Columns


Well-Known Member
Here is a VBA solution

Option Explicit

Sub ABC()
    Dim crit As String
    crit = Range("D6")
    Dim i As Long
    Dim lr As Long, lr2 As Long
    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 4 To lr
        lr2 = Range("D" & Rows.Count).End(xlUp).Row
        If Range("A" & i) = crit Then
            Range("D" & lr2 + 1) = Range("B" & i)
        End If
    Next i
    For i = 4 To lr
        lr2 = Range("D" & Rows.Count).End(xlUp).Row
        If Range("B" & i) = crit Then
            Range("D" & lr2 + 1) = Range("A" & i)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub


Im sorry Alan need solution with formula not clear in the first place but thanks for your positive feedback


Well-Known Member
To make VBA work, if that is your issue:

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button