Page 175 - Finanancial Management_2022

or this article, we take a familiar modelling INDEX
scenario and extend it (please refer to the Essentially, INDEX(array, row_number, [column_number])
downloadable Excel file for a modelled example). returns a value or the reference to a value from within a table
Imagine you had a dataset similar to that shown or range (list).
Fin the screenshot “Example Dataset”. For example, INDEX({7,8,9,10,11,12},3) returns the third item
in the list {7,8,9,10,11,12},ie, 9. This could have been a range:
Example dataset INDEX(A1:A10,5) gives the value in cell A5, etc.
INDEX can work in two dimensions as well (hence the
column_number reference) — see the screenshot “Table Array”.

Table array

Your task is a simple one: For any given month and any
given item, return the corresponding value (a so-called two-
way lookup). I shall ignore XLOOKUP, as it’s not in all versions INDEX(F11:L21,4,5) returns the value in the fourth row, fifth
of Excel presently, so I’ll use the functions INDEX and MATCH column of the table array F11:L21 (clearly 26 in the “Table Array”
instead. As a reminder: screenshot).

October 2022 I FM MAGAZINE I 17