Here’s how to pull a section of a vertical list and show it horizontally in Excel:
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
This process is much more easily described in my video, so please take a few minutes to watch it to get the full detail of the formula. I will describe below the general outline of what I did and the tools I used.
The challenge was to have a vertical list (in this case the list of our 50 states in alphabetical order), and to be able to enter any of the states in a cell and have the next 10 list out horizontally in order.
Here is the formula I used:
Here is the list of functions that I used:
Here is the syntax for each of those functions:
Here is a brief explanation of how I used each function:
IFERROR: I used it to wrap around my formula so that if I entered an incorrect state or had no entry at all, Excel would just enter a blank.
OFFSET: This is the primary function that is used in this formula. Offset allows you to establish a reference point, then you need to tell it, from that point, how many rows down and how many columns over to go to retrieve the result. In this case, I used the INDIRECT and MATCH functions to determine the reference point, the COLUMNS function to determine the number of rows down, and just entered “0” for the number of columns over, since our list was only one column wide.
INDIRECT: The INDIRECT function allows us to compile a text string that emulates a cell reference and convert it to an actual cell reference. In this case, I concatenated “States!$A$” with the MATCH function to create a cell reference, and INDIRECT converted that to the actual cell reference. “States!” indicates the worksheet and $A$ plus the number that MATCH generates is the actual cell.
MATCH: I used MATCH to find the state name on the list of states on the States worksheet. Match will return the sequence number of the item in the list, which in our case corresponds to the row number.
COLUMNS: By making the first cell reference in the range absolute and the second relative, as I copied the formula across the 10 columns, it returned a number from 1 to 10. This indicated to the OFFSET function how many rows down from the reference point I needed to go to return the appropriate state.
Ultimately it is quite logical if you follow along step by step. Again, if you haven’t taken the 10 1/2 minutes to watch the video, it may be worth your while.