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.

**Table of Contents**hide

## Download Practice Workbook

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

## 4 Suitable Ways to Concatenate with VLOOKUP in Excel

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

**Read More: ****How to Concatenate Formula and Text in Excel (4 Examples)**

### 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 Names in Excel (5 Easy Ways)**

**Similar Readings**

**How to Concatenate String and Variable in Excel VBA (A Detailed Analysis)****Combine Multiple Cells Into One Cell Separated By Comma In Excel (Functions & VBA)****Opposite of Concatenate in Excel (4 Options)****How to Concatenate Two Columns In Excel (5 Methods)****Concatenate Multiple Cells in Excel (7 Easy Ways)**

### 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 Multiple Cells with Comma in Excel (4 Ways)**

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

**Read More: ****Combine Text in Excel (8 Suitable Ways)**

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

## Conclusion

I hope all of the suitable methods mentioned above to c**oncatenate 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 Decimal Places in Excel (5 Examples)****Concatenate If Cell Values Match in Excel (7 Easy Ways)****How to Concatenate Cells with If Condition in Excel (5 Examples)****Excel CONCATENATE Showing Formula Not Result (5 Solutions)****How to Concatenate Arrays in Excel (With 2 Conditions)****Concatenate and Keep Number Format in Excel****How to Concatenate Rows in Excel (11 Methods)**