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 which is the return of 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
