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.**

**Read More: **How to Concatenate Cells with If Condition in Excel

### 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.

**Download the Practice Workbook**

## Related Articles

- How to Concatenate If Cell Values Match in Excel
- How to Concatenate Email Addresses in Excel
- How to Concatenate Decimal Places in Excel
- How to Concatenate Different Fonts in Excel
- Combine CONCATENATE & TRANSPOSE Functions in Excel