Vlookup using 2 columns to reference another

Question

I am trying to do a vlookup under a circumstance of first then last name to get an age. This will be done within Column A, then Column B. If found in Column A, Continue to Column B, If found in Column B, put age in J3 that comes from Column C else put "None".

Here is an example:

J1 = John
J2 = Doe
J3 = =VLOOKUP J1 & J2,A1:C50,3,FALSE)

J3 is what I have so far. Do I need to nest a Vlookup to check Column A, then Column B in order to get the age?

Here is an example of the table list:

A     B    C
Jeff  Vel  80
John  Fly  25
Jake  Foo  20
John  Doe  55

J3 = 55.

Answer 1

This is an alternative using VBA in a UDF

---

Based on this answer

---

This is a generic User Defined Function (UDF)

Steps

1. Add your data to an Excel structured table

2. Add this function to a Module

Code:

Public Function lookupValues(ByVal table As Range, ByVal criteria1_header As String, ByVal lookup_criteria1 As String, ByVal criteria2_header As String, ByVal lookup_criteria2 As String, ByVal return_header As String) As Variant
       
    On Error GoTo CleanFail
    
    ' Get criteria 1 column number from headers
    Dim criteria1Column As Long
    criteria1Column = Application.Match(criteria1_header, table.ListObject.HeaderRowRange, False)
    
    ' Get criteria 2 column number from headers
    Dim criteria2Column As Long
    criteria2Column = Application.Match(criteria2_header, table.ListObject.HeaderRowRange, False)
    
    ' Get value column number from headers according to function parameter
    Dim returnColumn As Long
    returnColumn = Application.Match(return_header, table.ListObject.HeaderRowRange, False)
    
    
    ' Get criteria 1 column values into 1d array
    Dim criteria1Values As Variant
    criteria1Values = WorksheetFunction.Transpose(Application.Index(table.Columns(criteria1Column), 0, 1))
    
    ' Get criteria 2 column values into 1d array
    Dim criteria2Values As Variant
    criteria2Values = WorksheetFunction.Transpose(Application.Index(table.Columns(criteria2Column), 0, 1))
    
    ' Define and redimension an array to hold the concatenated criteria 1 and criteria 2 values
    Dim criteria1_2Values() As Variant
    ReDim criteria1_2Values(1 To UBound(criteria1Values))
    
    ' Concatenate the criteria 1 and criteria 2 values and store them in an array
    Dim counter As Long
    For counter = 1 To UBound(criteria1Values)
        criteria1_2Values(counter) = criteria1Values(counter) & "|" & criteria2Values(counter)
    Next counter
    
    ' Get the matching row according to lookup values
    Dim resultRow As Variant
    resultRow = Application.Match(lookup_criteria1 & "|" & lookup_criteria2, criteria1_2Values, False)
    
    ' Get the result value according to the value column number
    Dim result As Variant
    result = Application.Index(table.Columns(returnColumn), resultRow)
    
    ' Return the value
CleanExit:
    lookupValues = result
    Exit Function
    
CleanFail:
    result = "Check function parameters"
    GoTo CleanExit
End Function

3. Call the function like this:

=lookupValues(TableName;E1;F1;E2;F2;E3)

Answer 2

Many ways:

If one has the new Dynamic array formulas:

=FILTER(C:C,(A:A=J1)*(B:B=J2))

If not then:

1. Dealing with Number returns:

If your return values are numbers and the match is unique(there is only one John Doe in the data) or you want to sum the returns if there are multiples, then Using SUMIFS is the quickest method.

=SUMIFS(C:C,A:A,J1,B:B,J2)

---

2. With non numeric returns

If the returns are not numeric or there are multiples then there are two methods to get the first match in the list:

a. A helper column:

In a forth column put the following formula:

=A1&B1

and copy down the list

Then use INDEX/MATCH:

=INDEX(C:C,MATCH(J1&J2,D:D,0))

b. The array formula:

If you do not want or cannot create the forth column then use an array type formula:

=INDEX(C:C,AGGREGATE(15,6,ROW($A$1:$A$4)/(($A$1:$A$4=J1)*($B$1:$B$4=J2)),1))

Array type formulas need to limit the size of the data to the data set.

If your data set changes sizes regularly we can modify the above to be dynamic by adding more INDEX/MATCH to return the last cell with data:

=INDEX(C:C,AGGREGATE(15,6,ROW($A$1:INDEX($A:$A,MATCH("ZZZ",A:A)))/(($A$1:INDEX($A:$A,MATCH("ZZZ",A:A))=J1)*($B$1:INDEX($B:$B,MATCH("ZZZ",A:A))=J2)),1))

This will allow the data set to grow or shrink and the formula will only iterate through those that have data and not the full column.

The methods described above are set in the order of Best-Better-Good.

---

3. To get multiple answers in one cell

If you do not want to sum, or the return values are text and there are multiple instances of John Doe and you want all the values returned in one cell then:

a. If you have Office 365 Excel you can use an array form of TEXTJOIN:

=TEXTJOIN(",",TRUE,IF(($A$1:$A$4=J1)*($B$1:$B$4=J2),$C$1:$C$4,""))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

Like the AGGREGATE formula above it needs to be limited to the data set. The ranges can be made dynamic with the INDEX/MATCH functions like above also.

b. If one does not have Office 365 Excel then add this code to a module attached to the workbook:

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

Then use the TEXTJOIN() formula as described above.