Populating BS, PNL, notes, and schedules from groupings

You will now populate the statements from the groupings using the XLOOKUP function. In this function, you will need to specify Lookup Array and Return Array, so to simplify our formula, we will name those ranges as well:

1. On the Groupings worksheet, select ACC GRP (Column B) from the first item beneath the header (Row 4) to five rows below the grand total (Row 29). In other words, B4:B29:

Figure 12.15 – XLOOKUP lookup array

2. Name this range LARR. This is the lookup array. The reason we extend it by five rows is in case we are required to expand our WTB with additional accounts.
3. Do the same for the return array, C4:C29, and name this range RARR:

Figure 12.16 – XLOOKUP return array

We can now populate the statements using XLOOKUP, starting from the schedules to the accounts:

Figure 12.17 – XLOOKUP syntax

When you...