Excel 2010 comparing multiple columns (2 columns to 2 other columns)

Question

I want to compare 2 columns to 2 other columns.

The thing is that I want to be able to search the whole column for the second set of 2 columns. For instance, values in C2 and D2 could match values in I23 and J23 or I101 and J101.

I found out about VLOOKUP having that feature for comparing one column to another.

=VLOOKUP(C2;$I$2:$K$343;3)

In the function above, C2 value is looked up into I column (from cell 2 to 343) and if found in that column, it'll return the value of the 3rd cell at the right of the matching cell.

Combined with this question's answer How to compare multiple columns in excel? that could work but I'm looking for a "clean" way to do this.

Thanks in advance

Answer 1

Here is a User Defined Function that will perform a 2 column lookup. Treat it like a vlookup.

LookupPair is a pair of cells. (C2:D2) in your example. Anything other that a pair of side-by-side cells will cause an error.

LookupRange is the columns that include both the matching pair columns and the return column. Something like (I1:K101) in your example. LookupRange must contain at least two columns or an error is generated.

ReturnCol is the column number in LookupRange that contains the value to be returned. (same as Col_index_num in a vlookup).

The function only does exact matches.

Function DoubleColMatch(LookupPair As Range, LookupRange As Range, ReturnCol As Integer) As Variant
Dim ReturnVal As Variant
Dim Col1Val As Variant
Dim Col2Val As Variant
Dim x As Long

    If LookupPair.Rows.Count > 1 _
    Or LookupPair.Columns.Count <> 2 _
    Or LookupRange.Columns.Count < 2 _
    Or ReturnCol < 1 _
    Or ReturnCol > LookupRange.Columns.Count _
    Then
        ReturnVal = CVErr(xlErrRef)
    Else
        Col1Val = LookupPair.Cells(1, 1)
        Col2Val = LookupPair.Cells(1, 2)

        ReturnVal = CVErr(xlErrNA)

        For x = 1 To LookupRange.Rows.Count
            If LookupRange.Cells(x, 1) = Col1Val _
            And LookupRange.Cells(x, 2) = Col2Val Then
                ReturnVal = LookupRange.Cells(x, ReturnCol).Value
                Exit For
            End If
        Next x

    End If

    DoubleColMatch = ReturnVal
End Function

Answer 2

A simple variant would be to use the function CONCATENATE() to concatenate a search string from the two columns. Afterwards you can compare the columns with various functions VLOOKUP(), MATCH().....