How to do HLOOKUP and return information vertically? FILE ATTACHED....

[alexx579]

Hi all, I have a list of Toyota car names (Column A) and in subsequent columns, I have model years for each of the car names. Some car names have 10 or 11 model years, some only 1 or 2. I want to be able to display the data dynamically in a purely vertical display.

I have the file attached, with the first tab the way the information is currently laid out, and a second tab showing what how I want to display it (did it for first two car names).

The only catch is though, I need it to be dynamic. As it is currently laid out, the names and years are dynamic in a separate report. But displaying it vertically is the challenge here. I would prefer a formula though.

Any help is appreciated!!! Thank you y'all....

[CK76]

Since you have Office 2010. Are you able to download and install PowerQuery? This will be easiest to maintain and more robust than other methods.

Steps:
1. Add headers to all columns with data. (i.e. Year1 in B1 then drag it over to Column L in your sample).
2. Convert range to Excel Table
3. Using PowerQuery, load from Table/Range, and hit Edit.
4. Once in PQ editor, select Year1 to Year11 columns.
5. Right click and hit "Unpivot"
6. Remove "Attribute" column and rename other columns appropriately.
7. Load result to either existing sheet or new sheet. Done.

Edit: Have a read of link for how to install PowerQuery.
https://www.excelcampus.com/install-power-query/

[alexx579]

Sorry CK76, I should have updated it. I am running Office 2016....is there a formula that can do this? I need something a bit more automated....

[CK76]

PowerQuery comes standard with Office 2016, but now called "Get & Transform". It replaces legacy Get External Data (i.e. MS Query).

PQ can be automated far better than formula (with minor change to accommodate for column extending beyond Column L), after initial setup.

What's the maximum number of columns needed?

Another option is using VBA. But it will be harder to maintain if some condition changes (as it has no UI and does not preserve intermediate steps like PQ does).