How to use VLOOKUP and TRANSPOSE functions in Excel

VLOOKUP is a function that allows you to look up a value in the leftmost column of a table and return a value in the same row from another column. TRANSPOSE is a function that allows you to convert a vertical range of cells to a horizontal range or vice versa. In this article, you will learn how to combine these two functions to perform a lookup and transpose operation in Excel.

The syntax of the VLOOKUP function is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

where:

• lookup_value is the value you want to find in the first column of the table.
• table_array is the range of cells that contains the table of data.
• col_index_num is the number of the column in the table from which you want to return a value.
• range_lookup is an optional argument that specifies whether you want an exact match (FALSE) or an approximate match (TRUE or omitted).

The syntax of the TRANSPOSE function is:

=TRANSPOSE(array)

where:

• array is the range of cells that you want to transpose.

Procedures

To use VLOOKUP and TRANSPOSE functions together, you need to follow these steps:

1. Select the range of cells where you want to paste the transposed table.
2. Enter the formula =VLOOKUP(lookup_value, TRANSPOSE(table_array), col_index_num, [range_lookup]) in the formula bar. Note that you need to use absolute references for the table_array argument, such as $B$1:$E$3.
3. Press Ctrl + Shift + Enter to enter the formula as an array formula. This will fill the selected range with the transposed values.
4. Adjust the lookup_value, col_index_num, and range_lookup arguments as needed for each cell in the range.

Example

Suppose you have the following table of data in the range B1:E3:

You want to transpose this table and look up the sales amount for each product and month in the range A7:C9:

To do this, you can use the following formula in cell B8:

=VLOOKUP($A8, TRANSPOSE($B$1:$E$3), 2, FALSE)

This will return the value 100, which is the sales amount for product A in January. You can then copy this formula to the rest of the range to get the following result:

Other approaches

There are other ways to perform a lookup and transpose operation in Excel, such as:

• Using the INDEX and MATCH functions, which can handle both horizontal and vertical lookups without transposing the table. For example, the formula in cell B8 could be:

=INDEX($B$2:$E$3, MATCH($A8, $B$1:$E$1, 0), MATCH(B$7, $B$2:$B$3, 0))

• Using the Paste Special option, which can transpose a table one time without using formulas. To do this, you need to:
  1. Copy the original table (B1:E3).
  2. Right-click over the top-left cell of where you want to paste the transposed table (A7), and then choose Transpose.
  3. Delete the original table if needed.