This article explains how to convert Excel columns to rows, also knows us unpivoting, using VLOOKUP formula. Unpivoting is the opposite of pivoting data (see Pivot Tables) by distributing values from a single column to multiple columns while keeping unique values in an anchor column.
Syntax
Unique lookup value =lookup value & COUNTIF(expanding range of lookup values, lookup value again)
=VLOOKUP(Unique lookup value, table we look for, column number of where data is, 0 or FALSE for exact match)
Steps
- Add a new column at left of your table and select its first cell
- Type the formula that generates a unique value =E3&COUNTIF($E$3:E3,E3)
- Copy down the formula to the entire column
- Write numbers from 1 to maximum number for a unique value into rows as titles of a new table 1, 2, 3
- Write anchor values in the first column of the new table Panama City, Gainesville, Valdosta
- In VLOOKUP function, use combination of anchor values and order numbers to replicate the unique values in the main table =VLOOKUP($B16&C$15,$B$3:$D$10,2,0)
- Wrap the function with IFERROR to remove #N/A errors =IFERROR(VLOOKUP($B16&C$15,$B$3:$D$10,2,0),"")
How
The first step in the process to convert Excel columns to rows is creating unique values for the VLOOKUP function. To ensure uniqueness, we use COUNTIF function. You can also use COUNTIFS because we only need single criteria and both work with the same logic. The trick is to use an expanding range ($D$3:D3). The expanding range uses mixed references (absolute and relative) to expand from an anchor cell when you copy it down.
The COUNTIF function with an expanding range produces running count values with each added row. Merging the actual values -location names in our example- with running count numbers generates unique values (Gainesville1, Valdosta1, Valdosta2, etc.) that can be used with VLOOKUP.
=D3&COUNTIF($D$3:D3,D3)
Next step is to create titles for the new table. We mimic unique values by merging titles to use in VLOOKUP.
The final step is to use the VLOOKUP function, but with unique values this time. So, do not forget to merge the index number with actual lookup value before using VLOOKUP. The unique values are the combination of column values and row values. It is important to lock column reference for column titles while doing the same for rows.
=VLOOKUP($B16&C$15,$B$3:$D$10,2,0)
Additionally; we advise to wrap the formula with the IFERROR function to eliminate any #N/A! errors.
=IFERROR(VLOOKUP($B16&C$15,$B$3:$D$10,2,0),"")
