How to Concatenate with VLOOKUP in Excel (4 Easy ways)

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.


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.

excel concatenate vlookup


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.

Apply Ampersand Operator to Concatenate with VLOOKUP in Excel

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

Apply Ampersand Operator to Concatenate with VLOOKUP in Excel

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.

Use CONCATENATE Function For Applying VLOOKUP with Concatenation

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

Use CONCATENATE Function For Applying VLOOKUP with Concatenation

Read More: How to Concatenate Names in Excel (5 Easy Ways)


Similar Readings


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.

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

  • 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

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

  • 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

Run Excel VBA Code to Concatenate with VLOOKUP

  • 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 
			

Run Excel VBA Code to Concatenate with VLOOKUP

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

Run Excel VBA Code to Concatenate with VLOOKUP

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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo