01 / 14Eleven Lines Priced at Zero

CompatibilityXLOOKUPError Handling

Excel #NAME? Error: XLOOKUP in Older Versions Explained

The Subcontract Order Came Back £38,415.60 Light and Every Rate on Our Copy Was Right, Because Their Excel Had Never Heard of XLOOKUP

24 Oct 202615 min read

UsesXLOOKUPINDEX MATCHIFERRORIFNA

Thorneley Shopfitting priced 48 lines of joinery at £412,860.00. The order Cadogan Build issued three weeks later said £374,444.40. Both figures came off the same workbook and nobody had changed a rate.

The difference is £38,415.60, and all of it is eleven lines that came back at zero. Every one of those eleven looked its rate up with a function the main contractor's Excel does not have.

The schedule was written in Microsoft 365 and recalculated in Excel 2016. A build that lacks a function stores it with an _xlfn. prefix and returns #NAME?, and the IFERROR around each lookup turned every one of those errors into a rate of nothing.

The tenderFigure
Priced on our copy£412,860.00
Ordered on theirs£374,444.40
Difference£38,415.60
Lines that priced at zero11 of 48
Recoverable as a variation£11,380.00

Scope. XLOOKUP, XMATCH, LET, FILTER, TEXTSPLIT, VSTACK, IFERROR, IFNA and FORMULATEXT, across Excel 2016, 2019, 2021, 2024 and Microsoft 365, on Windows and Mac.

Version numbers here are the perpetual releases, the ones bought once and kept. Microsoft 365 moves on a channel, so two colleagues both on 365 can still disagree about whether a function exists.

Throughout, column A holds the line code, column B the quantity and column C the rate; Codes and Rates are the two columns of the rate card.

01Eleven Lines Priced at Zero

Thorneley prices work from a rate card: one sheet of codes and rates, kept by the commercial manager and read by every schedule the estimators build. Forty-eight lines went out on this tender.

The estimator had rewritten the rate cell that spring. =IFERROR(XLOOKUP(A2,Codes,Rates),0) replaced a VLOOKUP that needed the code column to sit on the left, and on his machine it was a better formula in every way that mattered.

Thirty-seven lines still carried the old VLOOKUP, a hand-typed rate, or an INDEX and MATCH pair from an older schedule. Eleven had been converted, and the conversion is the only thing separating the lines that priced from the lines that did not.

Cadogan's quantity surveyor opened the file in Excel 2016, changed four quantities after a scope reduction, and read the new total off the bottom of the sheet. £374,444.40 went onto the bid summary and into the subcontract order.

Nothing on his screen was an error. Eleven rate cells read 0.00, on a schedule where a nil rate is something a subcontractor sometimes writes on purpose.

Eleven Lines That Priced at Nothing on Somebody Else's Excel

Thorneley Shopfitting's priced schedule of rates for the Cadogan Build fit-out, in A1:F16. Column C is the line total as it came back on the main contractor's copy, recalculated in Excel 2016; column D is the same line on the estimator's Microsoft 365 machine, where it has always been right. Column B names the function in the rate cell, and every row where the two copies disagree is a row whose rate was looked up with a function Excel 2016 has never had. The two rows at the bottom are the whole tender and the single line still recoverable as a variation.

ABCDEF
1
Line
Rate cell uses
On their copy
On ours
Short by
What happened
2
L-02 Softwood studwork
VLOOKUP
7709.6
7709.6
0
419 metres at £18.40. The oldest formula on the schedule and the only kind the contractor's Excel could read: a VLOOKUP down a sorted rate card, written in 2019 and never touched since.
3
L-04 Fire doors FD30
XLOOKUP
0
11380
11380
40 doors at £284.50, the largest line on the tender and the largest single loss. It was converted to XLOOKUP because the rate column had been moved to the left of the code column, which VLOOKUP cannot do.
4
L-05 Ironmongery sets
XLOOKUP
0
4837.5
4837.5
50 sets at £96.75. Priced in the same afternoon as the fire doors and converted in the same pass, so the two lines that carry a third of the loss failed for exactly the same reason.
5
L-07 Reception desk
INDEX/MATCH
7420
7420
0
One bespoke desk at £7,420.00, lifted from a schedule written three years earlier where the lookup was an INDEX and MATCH pair. The oldest formula on the sheet is the one that still worked.
6
L-09 Wall panelling
LET + XLOOKUP
0
9345
9345
150 square metres at £62.30. The LET held the rate once and used it twice, for the panel and for the beading, so the line needed two functions the contractor's build does not have.
7
L-13 Vanity units
XLOOKUP
0
5016
5016
12 units at £418.00. A plain XLOOKUP with no fallback argument, wrapped in IFERROR by the template the estimator copied the row from.
8
L-17 Window boards
XMATCH
0
2012.4
2012.4
86 metres at £23.40. XMATCH found the row and INDEX returned the rate; the INDEX would have been fine on its own and the XMATCH is what broke the pair.
9
L-21 Skirting, MDF
XLOOKUP
0
1503.5
1503.5
310 metres at £4.85. A small rate over a long run, which is the shape that hides best: nobody reading the schedule expects skirting to be one of the expensive lines.
10
L-26 Door frames, hardwood
LET + XLOOKUP
0
1713.6
1713.6
28 frames at £61.20. Converted in the same session as the panelling, and the LET was there to keep the formula readable rather than to do anything the sheet needed.
11
L-30 Shelving, birch ply
FILTER
0
831.6
831.6
44 metres at £18.90. A FILTER returning the one rate that matched the code, which is a dynamic array: on the contractor's build it has no function and nowhere to spill even if it had.
12
L-34 Handrail, oak
XLOOKUP
0
1002.25
1002.25
19 metres at £52.75. One of four lines added the evening before the tender went out, all of them written in the estimator's current style rather than the schedule's.
13
L-39 Access panels
XMATCH
0
350.4
350.4
24 panels at £14.60. The smallest line that failed, and the one that proves the fault is not about money: the formula was converted, so the rate went to zero.
14
L-45 Making good
XLOOKUP
0
423.35
423.35
A lump sum of £423.35 for making good after the second fix. A line that reads like a rounding allowance and was never going to be queried at nil.
15
The 48 lines
11 came back zero
374444.4
412860
38415.6
What the subcontract order said against what the schedule priced. Thirty-seven lines were read correctly by an Excel five years older than the formulas; the eleven that were converted this spring all priced at nothing.
16
Recoverable
L-04 re-measured
0
11380
11380
The fire doors were re-measured under a later variation, so that one line can be priced again at the rate on the card. The other £27,035.60 is inside a signed order.

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

02What an Older Excel Does With a Missing Function

A workbook stores the name of every function it uses. When Excel saves a function that earlier versions never shipped, it writes that name with an _xlfn. prefix, so an older build can open the file without inventing a meaning for it.

Open that file in the older build and the cell shows =_xlfn.XLOOKUP(A2,Codes,Rates). The name is not a function there, so the cell returns #NAME? — the same error you get from a misspelling.

Nothing is corrupted. Your copy still calculates, their copy still holds the whole formula, and saving from the older build keeps the prefix, so the file prices correctly again the moment it is back on a 365 machine.

On your 365 machineOn their Excel 2016
=XLOOKUP(A2,Codes,Rates)=_xlfn.XLOOKUP(A2,Codes,Rates)
284.50#NAME?
=IFERROR(XLOOKUP(A2,Codes,Rates),0)0.00

Scenario: Ask the next person you send a priced sheet to for a screenshot of one rate cell with the formula bar showing. An _xlfn. anywhere in that bar means their Excel is not pricing your file.

Try it in the grid

03Why the File Looked Right Until It Didn't

Excel saves the last calculated value of every formula cell alongside the formula itself. Open a workbook in a build that cannot evaluate it and those stored values are what you see, which is why a quick look at a file you have received proves nothing at all.

The values do not last. A workbook last saved by a different version of Excel is usually recalculated in full as it opens, and anything you then edit recalculates every cell that depends on it.

Cadogan's surveyor did both. He opened a 365 file in Excel 2016 and changed four quantities, which is the most ordinary thing anybody does to a priced schedule.

A received file that still looks right has not been tested. It has been opened by somebody who has not typed in it yet.

Scenario: Open a copy of your own schedule and press Ctrl+Alt+F9 to force a full recalculation. Anything that moves is a cell whose value was cached rather than calculated, and on somebody else's Excel it is a cell at risk.

Try it in the grid

04IFERROR Turned #NAME? Into a Price of Zero

IFERROR catches every error Excel has: #N/A, #VALUE!, #REF!, #DIV/0! and #NAME? alike. It was written around the lookup to deal with a code that is not on the rate card, and it dealt with a missing function the same way.

IFNA catches #N/A and nothing else. Swap one for the other and an unknown code still returns your zero, while a function the build does not have comes through as the error it actually is.

=IFNA(XLOOKUP(A2,Codes,Rates),0)

That is the whole fix for the quiet half of this fault. Those eleven cells would have read #NAME? on the surveyor's screen, and a column of #NAME? in a schedule of rates gets a phone call inside the hour.

WrapperCode not on the cardFunction not in their Excel
=XLOOKUP(A2,Codes,Rates)#N/A#NAME?
=IFNA(XLOOKUP(A2,Codes,Rates),0)0.00#NAME?
=IFERROR(XLOOKUP(A2,Codes,Rates),0)0.000.00

ERROR.TYPE names what a wrapper caught. =ERROR.TYPE(C2) returns 7 for #N/A and 5 for #NAME?, which is the cell to put beside a fallback you are not sure about.

Scenario: Find every IFERROR in your schedule that wraps a lookup, and change the ones whose only expected failure is a missing code to IFNA. Type one bad code afterwards and check the zero still arrives.

Try it in the grid

05Which Function Needs Which Version

The eleven lines Thorneley lost used four functions, and only one of them was XLOOKUP. XMATCH priced two of them, LET priced two more, and a FILTER returning a single rate priced one.

What you wroteOldest Excel that has it
SUM, VLOOKUP, HLOOKUP, INDEX, MATCH, ROUNDExcel 97
SUMIFS, COUNTIFS, IFERROR, COUNTAExcel 2007
IFNA, FORMULATEXTExcel 2013
IFS, SWITCH, TEXTJOIN, CONCAT, MAXIFSExcel 2019
XLOOKUP, XMATCH, LET, FILTER, SORT, UNIQUEExcel 2021
LAMBDA, TEXTSPLIT, TEXTBEFORE, VSTACK, TAKEExcel 2024
GROUPBY, PIVOTBYMicrosoft 365 only

A perpetual licence does not move. Excel 2016 will never grow an XLOOKUP, and a client on a five-year replacement cycle is three releases behind whatever you installed this morning.

Microsoft 365 is not one version either. A function on the current channel can be months away from the semi-annual channel that most locked-down IT departments deploy.

Scenario: Write the functions your rate cells use in a note at the top of the schedule and check each one against the table. The oldest row anything on your sheet lands in is the oldest Excel that can price it.

Try it in the grid

06The @ and the # Travel Badly Too

Two symbols that 365 writes into formulas are functions underneath, and they get the same treatment. The @ of implicit intersection is stored as _xlfn.SINGLE, and the spill reference E2# is stored as _xlfn.ANCHORARRAY(E2).

A dynamic array formula has a second problem on top of the missing name. Older builds do not spill, so a FILTER or UNIQUE that fills ten cells on your machine has nowhere to put nine of them on theirs.

What 365 writesWhat Excel 2016 reads
@A2:A9_xlfn.SINGLE(A2:A9)
E2#_xlfn.ANCHORARRAY(E2)
=FILTER(Rates,Codes=A2)#NAME? in one cell

Anything that spills is a redesign rather than a substitution. One formula per row, entered down the column, is the shape that works on both machines.

Scenario: Search the formulas on a sheet you are about to send for a # or an @. Each one is a cell to rewrite as one formula per row before the file goes out.

Try it in the grid

07One Column Audits the Whole Schedule

FORMULATEXT returns a formula as text, which turns a formula into something you can search. =ISNUMBER(SEARCH("XLOOKUP",FORMULATEXT(C2))) is TRUE in every cell whose rate came from a lookup Excel 2016 does not have.

Fill it down the rate column and the eleven lines name themselves. The count belongs in one cell above the column, which is the number to read before anything is sent:

=ISNUMBER(SEARCH("XLOOKUP",FORMULATEXT(C2)))
=SUMPRODUCT(--ISNUMBER(SEARCH("XLOOKUP",FORMULATEXT(C2:C49))))
=COUNTIF(C2:C49,0)

One search per function is the honest version of this check. A column listing the names you have adopted this year — XLOOKUP, XMATCH, LET, FILTER, TEXTSPLIT, VSTACK — costs five minutes and catches all of them rather than the famous one.

The third line is the check from the other side. =COUNTIF(C2:C49,0) counts the rates that priced at nothing, and on a schedule where no line is genuinely free, any answer above zero is this article.

Scenario: Put =ISNUMBER(SEARCH("XLOOKUP",FORMULATEXT(C2))) beside your own rate column and fill it down. Every TRUE is a line that will price at whatever IFERROR says on an Excel older than 2021.

Try it in the grid

08The Checker Checks the Format, Not Their Excel

File ▸ Info ▸ Check for Issues ▸ Check Compatibility is the feature people expect to catch this, and it does not. It compares your workbook against older file formats — what an .xls cannot carry — rather than against the build on somebody else's desk.

Save as .xlsx, send it to a 2016 user, and the checker has nothing to say, because the format is fine. The function is the problem, and a file format does not record which Excel is about to open it.

So ask instead. "Which Excel are you on?" is a question any quantity surveyor, auditor or finance manager can answer, and the answer decides which functions you are allowed to use in the file you send them.

Write the answer on the sheet. A note reading "priced for Excel 2016" at the top of the schedule is the only version control a workbook that travels will ever get.

Scenario: Send one cell before you send the schedule: a workbook with =XLOOKUP("a",{"a"},{1}) in A1 and nothing else in it. If the reply says #NAME?, you know what you are pricing into while nothing is at stake.

Try it in the grid

09Equivalents That Price Right Everywhere

Every line on Thorneley's schedule has a version that works on Excel 2016, and most of them are the formula the estimator replaced.

Instead ofWrite
=XLOOKUP(A2,Codes,Rates)=INDEX(Rates,MATCH(A2,Codes,0))
=LET(r,XLOOKUP(A2,Codes,Rates),r*B2)A rate column, then =C2*B2
=FILTER(Rates,Codes=A2)=INDEX(Rates,MATCH(A2,Codes,0))
=TEXTBEFORE(A2," ")=LEFT(A2,FIND(" ",A2)-1)
=VSTACK(Jan:Dec!A2:F99)A Power Query append

=INDEX(Rates,MATCH(A2,Codes,0)) is the one to learn. It looks left as happily as right, it survives an inserted column, and it has been in every Excel since the nineties.

Power Query is the quiet winner on the stacking functions. Get & Transform has been built into Excel for Windows since 2016, so an append query refreshes on a build that cannot spell VSTACK.

Scenario: Rewrite one XLOOKUP rate cell as =INDEX(Rates,MATCH(A2,Codes,0)) and compare the two down the whole column. Any row where they disagree is a row relying on a match mode you have not replaced yet.

Try it in the grid

10Send Figures When Figures Are the Point

A priced tender is a set of numbers, not a model. If the recipient is going to read it, check it and order from it, send numbers: copy the sheet, Paste Special ▸ Values over itself, and send that copy.

Keep the version with the formulas. Two files, one that calculates and one that was sent, is also the only way to answer "what did we actually price?" six months later, when the order is placed and the rate card has moved twice.

Where they do need to work in the sheet, send the version written in functions their Excel has. That is a cost of doing business with a 2016 build, and it is a great deal cheaper than £38,415.60.

A PDF is not a substitute, it is a record. Send one alongside the workbook when the figure matters, because a PDF is the only copy of a schedule that cannot recalculate into something else.

Anything signed should be the PDF. Anything worked on should be the workbook whose functions the recipient's Excel actually has.

Scenario: Take your last priced schedule, copy the sheet, and paste it over itself as values. Check the total against the live copy, then look at what Paste Special left behind in the rate column.

Try it in the grid

11Mac, the Web and Round Trips

Excel for Mac on Microsoft 365 has the same functions as Windows on the same channel, so a Mac is not the risk here. The perpetual Mac releases behave like the Windows release of the same number.

Excel for the web carries the 365 list and recalculates as it opens, which makes it a useful test bench. If a file prices correctly in a browser and wrongly on a colleague's desktop, the colleague's build is the difference.

A round trip through Google Sheets is a second translation. Sheets has its own function list and its own names for parts of it, so a workbook that goes out as .xlsx, gets edited there and comes back is not the workbook you sent.

Scenario: Upload a copy of your schedule to Excel for the web and read the total. A figure that matches your desktop but not the recipient's tells you which machine to ask about.

Try it in the grid

12Eight Traps

  1. IFERROR around a lookup. It cannot tell a missing code from a missing function, and it answers both with your fallback.
  2. Trusting the values in a file you received. They are the sender's last calculation, and they survive only until something recalculates.
  3. Expecting the failure to be loud. A rate of 0.00 in a schedule of rates is a number rather than an error, and it foots.
  4. Reading _xlfn. as corruption. The file is intact; the prefix is how Excel keeps your formula safe in a build that cannot run it.
  5. Relying on the Compatibility Checker. It reads the file format, not the version of Excel that is about to open the file.
  6. Modernising formulas on a schedule that travels. The upgrade that makes your copy clearer is what makes their copy wrong.
  7. Sending a formula that spills. Older builds do not spill, so nine of ten results have nowhere to land.
  8. Assuming Microsoft 365 is one version. Channels differ by months, and the semi-annual channel is what most locked-down fleets run.

13Five Exercises

  1. Reproduce it. Price ten lines with =IFERROR(XLOOKUP(A2,Codes,Rates),0), then misspell XLOOKUP as XLOOKUPX and read the total. Those zeros are what Excel 2016 computes.
  2. Make it loud. Swap the wrapper for IFNA and watch the same ten lines return #NAME? instead of a price.
  3. Audit it. Fill =ISNUMBER(SEARCH("XLOOKUP",FORMULATEXT(C2))) down the rate column and count the TRUEs with SUMPRODUCT.
  4. Translate it. Rewrite each flagged cell as =INDEX(Rates,MATCH(A2,Codes,0)) and check that every line still prices the same.
  5. Send figures. Paste the priced sheet over itself as values, then confirm the total survives a full recalculation with Ctrl+Alt+F9.

What to Take Away

Thorneley's copy of this schedule has never priced a line wrong. It still totals £412,860.00 today, on the machine it was written on, against the rate card it was written from.

The figure that mattered was the one Cadogan's Excel produced. That build had never heard of three of the four functions the estimator adopted that spring, and it said £374,444.40 without putting a single error on the screen.

A file that leaves the building is run by somebody else's Excel. The functions in it are a promise about their machine rather than yours, and _xlfn. is Excel writing down that the promise was not kept.

IFNA in place of IFERROR would have cost nothing and turned eleven silent zeros into eleven #NAME? cells on the surveyor's screen. £27,035.60 of the £38,415.60 is inside a signed order and is not coming back.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge