Return to Excel Formulas List

VLOOKUP CONCATENATE – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to use VLOOKUP with a concatenated helper column in Excel and Google Sheets.

vlookup concatenate Main Function

VLOOKUP with Concatenation

You may want to perform a VLOOKUP on a data table without unique values in any single column.

vlookup concatenate 01

In this example, you could not use the VLOOKUP Function using the First Name or Last Name column, so you need to create a helper column concatenating some of your columns to create unique name values.

vlookup concatenate 02

In this example, we use the concatenating operator (&), but you could also use the CONCATENATE Function:

Once you have the helper column with distinct values, you can perform an exact match VLOOKUP. To get your lookup value for the formula, use the same concatenation as the helper column.

vlookup concatenate 03

 

VLOOKUP CONCATENATE in Google Sheets

These formulas work the same in Google Sheets as in Excel.

vlookup concatenate Google Function