# 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)`
• Press Enter on your keyboard. You’ll get the results.

## Bottom Line

You can pop up Microsoft Visual Basic for Applications window by pressingÂ Alt + F11 simultaneously.

If a Developer tab is not visible in your ribbon, go to File â†’ Option â†’ Customize Ribbon.

The #N/A!Â error arises when the formula or a function in the formula fails to find the referenced data.

The #DIV/0!Â error happens when a value is divided by zero(0) or the cell reference is blank.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF