Author: Oscar Cronquist Article last updated on February 09, 2018

The formula in cell B8 uses a named range to calculate the row and column needed to extract the correct value.

=INDEX(rng, MOD(ROW(A1)-1, ROWS(rng))+1, QUOTIENT(ROW(A1)-1, ROWS(rng))+1)

To create a named range simply select the cell range (B3:E5) and click in name box. Type a name for that range, I named the cell range rng. Press Enter, that's it.

Explaining formula in cell B8

The INDEX function needs a row and column number in order to get the correct value.

The ROWS function returns the number of rows in a cell range.

ROWS(rng) becomes ROWS(B3:E5) and returns 3.

The ROW function returns the row number of a cell reference.

ROW(A1) returns 1.

To calculate the row number I use the MOD function to build a repeating number sequence. There are three rows in B3:E5 so the sequence must be 1,2,3,1,2,3, ... and so on.

MOD(ROW(A1)-1, ROWS(rng))+1

becomes

MOD(1-1, 3)+1

becomes

MOD(0, 3) + 1 and returns 1.

To calculate the column number I use the QUOTIENT function to build a repeating number sequence: 1,1,1,2,2,2,3 ... and so on.

QUOTIENT(ROW(A1)-1, ROWS(rng))+1

becomes

QUOTIENT(1-1, 3)+1

becomes

QUOTIENT(0, 3)+1 and returns 1.

The INDEX function then returns the value in row 1 and column 1 from cell range 3:E5.

INDEX(rng, MOD(ROW(A1)-1, ROWS(rng))+1, QUOTIENT(ROW(A1)-1, ROWS(rng))+1)

becomes

INDEX(rng, 1, 1)

and returns "Veges are us" in cell B8.

Returning values row by row

The following formula gets the values row by row:

=INDEX(rng, QUOTIENT(ROW(A1)-1, COLUMNS(rng))+1, MOD(ROW(A1)-1, COLUMNS(rng))+1)

Download Excel *.xlsx file

Rearrange a cell range to vertically distributed values.xlsx

Related articles

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of [โ€ฆ]

Find numbers closest to sum

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to [โ€ฆ]

Dynamic scoreboard

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there [โ€ฆ]

Find closest value

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with [โ€ฆ]

Identify all characters in a cell value

Sometimes when you sort values you get unexpected results, the cause is probably unwanted characters in a cell. The same [โ€ฆ]