While working in Microsoft Excel, there are various formulas to make our work easier. The VLOOKUP formula is one of them. It has a wide range of applications in Excel. The VLOOKUP function is used to find out the exact match of the looking-up value. In this article, we will learn how to concatenate with VLOOKUP in Excel effectively with appropriate illustrations.

## How to Concatenate with VLOOKUP in Excel: 4 Easy Ways

Let us have a data set like this. We have the records of several people. We will look up a value using the **VLOOKUP****, ****CONCATENATE** functions, and the VBA code as well to perform concatenate in Excel. Hereâ€™s an overview of the dataset for todayâ€™s task.

### 1. Apply Ampersand Operator to Concatenate with VLOOKUP in Excel

In this method, we will apply the **VLOOKUP function** to concatenate cells along with the **Ampersand** sign. We can easily do that using the **VLOOKUP function **and **Ampersand **sign**.** Letâ€™s follow the instructions below to learn!

**Step 1:**

- Firstly, select cell
**E5,**and write down the below formula in that cell.

`=B5&" " & C5`

- Hence, simply press
**Enter**on your keyboard. As a result, you will get the output of the formula. The output is**Alfred Tennyson.**

- Further,
**AutoFill**the**formula**to the rest of the cells in column**E****.**

**Step 2:**

- Now, using the Concatenate column, we will apply
**the VLOOKUP function**to find out the exact match. To do that, select cell**D16**, and write down**the 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.**

- After that, simply press
**Enter**on your keyboard. As a result, you will get the exact match of**George**which is the return of the**VLOOKUP function**. The return is**George Eliot.**

### 2. Use CONCATENATE Function For Applying VLOOKUP with Concatenation

Now we will use the **VLOOKUP function** to concatenate cells along with the** CONCATENATE function.** Firstly, we will apply the** CONCATENATE function **to concatenate cells. Hence, apply the **VLOOKUP function** to look up a specific value. Letâ€™s follow the instructions below to learn!

**Step 1:**

- Firstly, select cell
**E5,**and write down the below**CONCATENATE function**in that cell.

`=CONCATENATE(B5," ",C5)`

- Further, simply press
**Enter**on your keyboard. As a result, you will get the output of the**CONCATENATE function**. The output is**Alfred Tennyson.**

- Further,
**AutoFill**the**CONCATENATE function**to the rest of the cells in column**E.**

**Step 2:**

- After that, applying the Concatenation column, we will apply the
**VLOOKUP function**to find out the exact match. To do that, select cell**D16**, and write down the**VLOOKUP function**in that cell.

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

- After that, simply press
**Enter**on your keyboard. As a result, you will get the exact match**the VLOOKUP function**. The return is**Edward Fokuama**.

**Read More: **How to Concatenate Arrays in Excel

### 3. Combination of IF, COUNTIF, ROW, INDEX, and SMALL Functions to Concatenate with VLOOKUP

In this section, you can know how many projects are shared among each pair of project managers by applying the **IF, COUNTIF, ROW, INDEX, **and **SMALL** functions. Hence, you can concatenate the projects with the project managers using the **Ampersand **symbol and the **CONCATENATE **function as well. Letâ€™s follow the instructions below to learn!

**Step 1:**

- First of all, select cell
**F5**and write down the below screenshotâ€™s functions in that cell. Hence, press**Enter**on your keyboard to get your desired output.

`=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.

- Then,
**AutoFill**the functions to the rest of the cells in column**F.**

**Step 2:**

- After that, we will concatenate a project and the project managers under that project. Insert the below formula into the
**formula bar**.

`=$E$5&" "&F5`

- Hence,
**AutoFill**the formula to the rest of the cells in column**G.**

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

### 4. Run Excel VBA Code to Concatenate with VLOOKUP

Now Iâ€™ll show you how to concatenate cells in Excel by using a simple VBA code. You can define your function using the VBA code in Excel and perform operations using it. Itâ€™s very helpful for some particular moments. From our dataset, we will concatenate cells with the **VLOOKUP function** in Excel. Letâ€™s follow the instructions below to learn!

**Step 1:**

- First of all, open a Module, to do that, firstly, from your
**Developer**tab, go to,

**Developer â†’ Visual Basic**

- Therefore, after clicking on the
**Visual Basic**ribbon, a window named**Microsoft Visual Basic for Applications – VLOOKUP Concatenate**will instantly appear in front of you. From that window, we will insert a module for applying our**VBA code**. To do that, go to,

**Insert â†’ Module**

**Step 2:**

- Hence, the
**VLOOKUP Concatenate**module pops up. In the**VLOOKUP Concatenate**module, write down 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
```

- Hence, run the
**VBA**To do that, go to,

**Run â†’ Run Sub/UserForm**

- Then, save that code with theÂ
**.xlsmÂ**extension usingÂ**Ctrl + SÂ**keyboard shortcut. - After that, go back to your worksheet and select cell
**C16,**and write down the below**user-defined function**which is created by**VBA**The**user-defined function**is,

`=VlookupCon(B16,B5:E13,2)`

- After that, press
**Enter**on your keyboard. Hence, you will get the return of the**user-defined function.**The return is**Charls Dickens Stuart Mill Alexandre Cardinal.**

## 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, you can make it visible. To do that, go to,

**File â†’ Option â†’ Customize Ribbon**

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

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

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

I hope all of the suitable methods mentioned above to concatenate with VLOOKUP will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

## Related Articles

- How to Concatenate If Cell Values Match in Excel
- Excel INDEX MATCH to Concatenate Multiple Results
- How to Concatenate Email Addresses in Excel
- How to Concatenate Decimal Places in Excel
- How to Add Parentheses with CONCATENATE Function in Excel
- How to Concatenate Different Fonts in Excel
- Combine CONCATENATE & TRANSPOSE Functions in Excel