Purpose

Look up values in a table, using Excel’s VLOOKUP and CONCATENATE functions. Concatenate is used to combine cell values together for the lookup.

Syntax — basic VLOOKUP — see more about the VLOOKUP function

= VLOOKUP ( ValueToLookup, TableRange of where to look, ColumnNumber of the table range to find the return value, True/False (1,0) — whether to take the closest match or to match the lookup value exactly)

Syntax — VLOOKUP with CONCATENATE

=VLOOKUP ( CONCATENATE ($F3 & $G$2 ), $B$3:$E$20, 4, 0)

Syntax description

• In the example above, the value to be looked up in the source range $B$3:$E$20 is created by combining (concatenating) the contents of cells F3 and G2.
• Note the mixture of absolute and relative cell references:
  • $F3 denotes absolute column ($ prefix to the column letter), relative row (no $ prefix to the row number). When the formula is copied down the column, the row number will increase; but if the formula is copied across to another column, the column letter will remain as ‘F’.
  • $G$2 denotes absolute column and absolute row ($ prefix before both elements of the cell reference). This cell reference will stay as G2 wherever it is copied to in the worksheet.
• The fourth column will be looked in for the value to be found.
• The (optional) final argument in the formula, specifies ‘1’=TRUE for approximate match or ‘0’=FALSE for an exact match. The default is ‘1’ TRUE if no value is entered.
• NOTE: One key rule about VLOOKUPs is that you need to be looking up a value in the first column of the table or range.

Note: CONCAT vs CONCATENATE

CONCAT replaces the CONCATENATE function, and is gradually being phased in to Office versions. It is available if you have an Office 365 subscription and have the latest version of Office; or on Windows or Mac if you have Office 2019.

With both CONCAT and CONCATENATE, the elements to be joined can be separated by a comma or an ampersand (, or &).

Method — use VLOOKUP with CONCATENATE (see video above).

---

In the video example we create a wide format table on the right based on the long format table on the left. We combine values in the ‘Year’ column and the ‘High’ and ‘Low’ column headers, to look up values in the source table, using a ‘helper column’.

1. Add a ‘helper column’ (which will be used to locate the correct row for the value to be found) to the long format table. This column has to be on the left hand side. The helper column contains the joined values in the ‘Year’ and ‘Rank’ columns.
2. You can use the ‘simple’ version of combining cell contents into one value, by entering ‘=CellRef1 & CellRef2’; e.g. =C5&D5. We’re only going to use this formula down the table column, so we can leave the cell references as relative (no $ prefixes).
3. Double-click the right-hand corner of the cell to fill down the column.
4. Next, enter the VLOOKUP with CONCATENATE formula into the first cell under the ‘High’ column header. Again, you can use the simple version of the cell references (as in 2. above), or wrap them inside the CONCATENATE function. We use a mixture of absolute and relative cell references, so that the formula works when it is filled down the column, and can be copied to the ‘Low’ column in the table.
5. Press enter to run the formula, and double-click the right-hand corner of the cell to fill the formula down the table column.
6. Copy the first cell and paste its formula into the top cell under the ‘Low’ column header.
7. Replace the absolute reference to the ‘High’ column heading, with the absolute reference to the ‘Low’ column heading, and fill this new formula down the table column. Because we used an absolute column reference for the ‘Year’ column, we don’t need to update this part of the formula; when we copy it to the ‘Low’ column, the reference to column H (Year) is fixed.