Transpose VLOOKUP in Excel

The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from the column you specify. By default, the table must be in a vertical range and sorted in ascending order.

Example

In some cases, however, the lookup table may be a horizontal range. This tutorial shows how to use the VLOOKUP function to return data from a horizontal range or transpose VLOOKUP in Excel.

This tutorial will show how to lookup data from the top vertical range and fill it into the bottom vertical range:

To achieve this, we need to use formulas that combine the VLOOKUP function and the TRANSPOSE function that converts a vertical range of cells to a horizontal range or vice versa.

Transpose the Second Row into the Second Column

To transpose the second row in the first horizontal data range into the second column of the second vertical data range, we use the following steps:

  1. Select cell B7 and type in the following formula:
Table

Description automatically generated
  1. Click the Enter button on the Formula bar to enter the formula. Double-click or drag down the fill handle to copy the formula down the column.

Transpose the Third Row into the Third Column

To transpose the third row in the first horizontal data range into the third column of the second vertical data range, we use the following steps:

  1. Select cell C7 and type in the following formula:
  1. Click the Enter button on the Formula bar. Double-click or drag down the fill handle to copy the formula down the column.
Table, Excel

Description automatically generated

Conclusion

This tutorial has shown how to transpose VLOOKUP in Excel. We hope you found the information useful.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.