How to Concatenate with VLOOKUP in Excel (4 Easy ways)

Consider the following dataset. We have the records of several people and will look up a value from the dataset using VLOOKUP and other functions. The content to search for is listed in a separate cell.

Method 1 – Apply the Ampersand Operator to Concatenate with VLOOKUP in Excel

Steps:

Select cell E5 and insert the following formula in that cell.

=B5&" " & C5

Press Enter on your keyboard and you will get the output of the formula.

AutoFill the formula to the rest of the cells in column E.

Select cell D16 and copy the following VLOOKUP function in that cell:

=VLOOKUP(B16,B5:E13,4,FALSE)

Formula Breakdown:

B16 is the lookup_value.
B5:E13 is the table_array.
4 is the col_index_num
FALSE is the exact match of the VLOOKUP function.

Press Enter. As a result, you will get the exact match of George which is the return of the VLOOKUP function.

Method 2 – Use the CONCATENATE Function for Applying VLOOKUP with Concatenation

Steps:

Select cell E5 and insert the following CONCATENATE function in that cell.

=CONCATENATE(B5," ",C5)

Press Enter to get your first result.

AutoFill the CONCATENATE function to the rest of the cells in column E.

Select cell D16 and use the VLOOKUP function in that cell to get an exact match.

=VLOOKUP(B16,B5:E13,4,FALSE)

Press Enter on your keyboard to get the match from the new column.

Read More: How to Concatenate Arrays in Excel

Method 3 – Combination of IF, COUNTIF, ROW, INDEX, and SMALL Functions to Concatenate with VLOOKUP

Let’s see how many projects are shared among each pair of project managers.

Steps:

Select cell F5 and insert the following function in that cell, then press Enter.

=IF(COUNTIF($B$4:$B$16,$E$5)>=ROWS($1:1),INDEX($C$4:$C$16,SMALL(IF($B$4:$B$16=$E$5,ROW($4:$16)),ROW(1:1))),"")

Formula Breakdown:

ROW($4:$16) will count the row number from 4 to 16.
$B$4:$B$16=$E$5 is the logical_test, and ROW($4:$16) is the [value_if_true] of the inside most IF function.
IF($B$4:$B$16=$E$5,ROW($4:$16)) is the array and ROW(1:1) is the position in the range of the data of the SMALL function.
Inside the INDEX function, $C$4:$C$16 is the reference, and SMALL(IF($B$4:$B$16=$E$5,ROW($4:$16)),ROW(1:1)) is the row_num of that function.
COUNTIF($B$4:$B$16,$E$5) will count the row number.
If the row number is greater than ROWS($1:1), it will return value.
Finally, the first IF function will return the TRUE value.

AutoFill the functions to the rest of the cells in column F.

We will concatenate a project and the project managers under that project. Insert the following formula into the formula bar for G5:

=$E$5&" "&F5

AutoFill the formula to the rest of the cells in column G.

Method 4 – Run Excel VBA Code to Concatenate with VLOOKUP

Steps:

Open a Module, by going to Developer and Visual Basic.

A window named Microsoft Visual Basic for Applications – VLOOKUP Concatenate will appear.
Go to Insert and choose Module.

The VLOOKUP Concatenate module pops up. Copy the below VBA code.

Function VlookupCon(lookupval, lookuprange As Range, colindex As Long)
Dim R As Range
Dim y As String
y = ""
For Each R In lookuprange
    If R = lookupval Then
        y = y & " " & R.Offset(0, colindex - 1)
    End If
Next R
VlookupCon = y
End Function

Run the VBA with Run followed by Run Sub/UserForm.

Save that file with the .xlsm extension using the Ctrl + S keyboard shortcut.
Go back to your worksheet and select cell C16.
Insert the following user-defined function which is created by VBA:

=VlookupCon(B16,B5:E13,2)