In my earlier blog ” Flipping from horizontal to vertical, journal entry” I was using INDEX function. However, I thought maybe the INDEX should have been introduced more profoundly first.

A sales report is downloaded from ERP to Excel.

The data should be coherent for pivot report. Date, product and sales volume need to be separated to own columns.

INDEX formula was made for this purpose, also for many other purposes, but at least for this case.

Range A3:D21 is defined as array. After array, row number and column numbers are entered. Row and columns are like coordinates with piece of information is taken from the array.

For example, in our array, array row 2 column 2 equals to 76. Our array row 4 col 1 equals to 2.1.2019.

This is how tacked the case. Report data is in report sheet and pivot data in pivot sheet.

The solution is built with two parts: columns A to C contain the data and columns E to J the column and row values for data.

The date is defined in E and F columns.

As the date is same for three consecutive rows. Therefore E-columns has a new value for every three rows. Row F has a fixed value 1 as the date is in the first column throughout in report data.

The product information is on the top of the report. The row values in G column is static one, column values in column H is varying from 2 to 4.

The sales volume begins in the second row. As sales data is side by side, I column changes value after having had three similar values. The column coordinate value in J column goes 2,3,4,2,3,4.

Here are the cell contents as I have written them.

The date is in A column. In the array the first date is in the second row and first column. B column is for product information. In the report the product is in the first row. The sales volume in C column is extracted starting from second row, columns two to four.

If you want to know how to change all the formula text visible instead of formula values, take file-options and then in advanced page display options for this worksheet, activate “Show formulas in cells instead of their calculated results”.

This blog was to clarify how INDEX function functions.