Help required on an Index Formula

mike.thorpe17421

I am trying to work out a formula that exists in a sheet created by somebody that is used for reporting although all of the data is corrupt. The formula is as follows:

=INDEX({Malta SSOT Range 1}, $[Column45]@row, Market$1)

Malta SSOT Range 1 is the source sheet and Column 45 is a column used in the sheet containing the formula.This contains numbers using a formula example being "=[Column45]5 + 1" which gives a number of 54. Another row being =[Column45]7 + 1 giving a number of 56.

All of the columns in the sheet containing the formula are similar to the Source sheet (except Column 45).

In the first row of the sheet containing the formula there are numbers, as indicated on the following screen shot.

In the formula for Market it should bring in the corresponding row and column items from the Source sheet.

Columns in the Source sheets have recently moved and new columns created so some may now be different to that of the sheet containing the formulas.

Can anybody offer any advice what the formula is truing to achieve and where the numbers in Column 45 are coming from and what the numbers in the first row are being used for.

Genevieve P.

Hi @mike.thorpe17421

I can help explain what I think the original formula was looking to do, however I believe fixing the error may have to do with all the changes that happened in the source sheet. Likely we just need to update your {range} and make sure the columns are in the correct order.

The structure of an INDEX Function is as follows:

=INDEX(range, row_index, column_index)

The Row and Column indexes will need to be in numerical format (e.g. row 1 = 1, or the 2nd column in your range = 2).

In your formula, you have range that spans multiple columns and rows in your other sheet:

=INDEX({Malta SSOT Range 1}

Let's pretend this {range} is only 3 rows and 3 columns. Say we were looking for row 1, but column 2, it would look like this:

=INDEX({Malta SSOT Range 1}, 1, 2)

Meaning:

=INDEX({Malta SSOT Range 1}, row 1, column 2)

However in your formula, instead of needing to manually type in 1 and 2 etc, you're referencing cells in the current sheet. To find what row to bring back, your formula is referencing a number in this current row:

=INDEX({Malta SSOT Range 1}, $[Column45]@row,

And for the column number, it's looking at the top row of this current column:

=INDEX({Malta SSOT Range 1}, $[Column45]@row, Market$1)

What I think may have happened is either the row number doesn't exist on your other sheet or the column number. Check the {Malta SSOT Range 1} range (click on it and select edit reference) to make sure it's spanning the correct number of columns, and that the numbers in the top row align with the correct values to bring back.

Let me know if that makes sense! If not, it would be helpful to see a screen capture of your other sheet, but please block out sensitive data.

Cheers,

Genevieve

Genevieve P.

Hi @mike.thorpe17421

I can help explain what I think the original formula was looking to do, however I believe fixing the error may have to do with all the changes that happened in the source sheet. Likely we just need to update your {range} and make sure the columns are in the correct order.

The structure of an INDEX Function is as follows:

=INDEX(range, row_index, column_index)

The Row and Column indexes will need to be in numerical format (e.g. row 1 = 1, or the 2nd column in your range = 2).

In your formula, you have range that spans multiple columns and rows in your other sheet:

=INDEX({Malta SSOT Range 1}

Let's pretend this {range} is only 3 rows and 3 columns. Say we were looking for row 1, but column 2, it would look like this:

=INDEX({Malta SSOT Range 1}, 1, 2)

Meaning:

=INDEX({Malta SSOT Range 1}, row 1, column 2)

However in your formula, instead of needing to manually type in 1 and 2 etc, you're referencing cells in the current sheet. To find what row to bring back, your formula is referencing a number in this current row:

=INDEX({Malta SSOT Range 1}, $[Column45]@row,

And for the column number, it's looking at the top row of this current column:

=INDEX({Malta SSOT Range 1}, $[Column45]@row, Market$1)

What I think may have happened is either the row number doesn't exist on your other sheet or the column number. Check the {Malta SSOT Range 1} range (click on it and select edit reference) to make sure it's spanning the correct number of columns, and that the numbers in the top row align with the correct values to bring back.

Let me know if that makes sense! If not, it would be helpful to see a screen capture of your other sheet, but please block out sensitive data.

Cheers,

Genevieve

mike.thorpe17421

Many thanks Genevieve. That makes sense as columns have moved, so will try correcting all of the formulas and see how we get on.

Really appreciate you taking the time to respond.

Kind regards

Mike