HLOOKUP

Why it works

The period code is in the first row of the selected block, which is the only place HLOOKUP searches. The row number is counted inside table_array, not from row 1 of the sheet, so if the extract started in row 10 the 3 would still mean the third row of the block.

More to watch out for

• If the codes in the top row carry a trailing space ("P4 "), the P4 in H2 never matches and you get #N/A for every period. Clean the header row with TRIM first.

Why it works

An exact match treats the top row as a list of keys: a year is either in it or it is not. IFNA reacts only to #N/A, which is exactly the signal for a missing year, so other errors such as #REF! stay visible. The $ signs fix the table while the lookup value moves down one row per copy.

More to watch out for

• IFNA also hides a typing slip. If I2 holds "FY 2024" or "FY2024 ", the message says No rate in table for a year that is in the table. Check the year cells with LEN when the message appears unexpectedly.
• Insert a new row between the year headers and Standard terms and the 2 now points at the new line instead of Standard terms. Count the rows again after any change to the table.

Variations

=XLOOKUP(I2, $B$1:$F$1, $B$2:$F$2, "No rate in table")

=XLOOKUP(I2; $B$1:$F$1; $B$2:$F$2; "No rate in table")

Why it works

MATCH turns a row label into the row number HLOOKUP needs, so the weakest part of HLOOKUP, the hard-coded row, follows the data instead of the formula. Both ranges start in row 1, which is why the MATCH position and the HLOOKUP row count line up. IFNA only reacts to #N/A, so a broken reference still shows as #REF! instead of being disguised as an unknown line.

More to watch out for

• If the label range starts in row 2 while the table starts in row 1, every result is one row off. Both ranges must start in the same row.
• One message covers two different failures. When the panel shows Not in budget table you do not know whether the month or the line was wrong, so check both cells before concluding that a figure is missing.

Variations

=IFNA(XLOOKUP(H1, $B$1:$F$1, XLOOKUP(H2, $A$2:$A$5, $B$2:$F$5)), "Not in budget table")

=IFNA(XLOOKUP(H1; $B$1:$F$1; XLOOKUP(H2; $A$2:$A$5; $B$2:$F$5)); "Not in budget table")

=IFNA(INDEX($B$2:$F$5, MATCH(H2, $A$2:$A$5, 0), MATCH(H1, $B$1:$F$1, 0)), "Not in budget table")

=IFNA(INDEX($B$2:$F$5; MATCH(H2; $A$2:$A$5; 0); MATCH(H1; $B$1:$F$1; 0)); "Not in budget table")

Why it works

Because row_index_num is a list, HLOOKUP returns one value per row number from the matching column, and SUM adds them. Microsoft documents only a single number for row_index_num; a list works but is not described on its help page.

Do not use this construction when:

• the block is fixed and you can sum it directly (SUM over the three cells of the quarter)
• lines are added or reordered often, because the row numbers are positions that do not follow the labels
• the reader must be able to see at a glance which lines are included

The alternatives are XLOOKUP, which returns the whole column of the quarter and lets you sum the lines you want, and INDEX with MATCH, which does the same in every version of Excel.

More to watch out for

• Insert a line above Pension contributions and the list 2, 3, 4 still points at the same positions, so the new line is counted and Pension contributions drops out of the total. The result still looks like a plausible amount. Recheck the list after any change to the table.
• LET needs Excel 2021 or Microsoft 365. In older versions, use the INDEX form, which does not depend on LET or on array handling.

Variations

=IFNA(SUM(INDEX($B$2:$E$4, 0, MATCH(H1, $B$1:$E$1, 0))), "Quarter not in table")

=IFNA(SUM(INDEX($B$2:$E$4; 0; MATCH(H1; $B$1:$E$1; 0))); "Quarter not in table")

=IFNA(SUM(XLOOKUP(H1, $B$1:$E$1, $B$2:$E$4)), "Quarter not in table")

=IFNA(SUM(XLOOKUP(H1; $B$1:$E$1; $B$2:$E$4)); "Quarter not in table")