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 tender | Figure |
|---|---|
| Priced on our copy | £412,860.00 |
| Ordered on theirs | £374,444.40 |
| Difference | £38,415.60 |
| Lines that priced at zero | 11 of 48 |
| Recoverable as a variation | £11,380.00 |
Scope.
XLOOKUP,XMATCH,LET,FILTER,TEXTSPLIT,VSTACK,IFERROR,IFNAandFORMULATEXT, 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;
CodesandRatesare 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.
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 machine | On 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.
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 grid04IFERROR 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.
| Wrapper | Code not on the card | Function 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.00 | 0.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.
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 wrote | Oldest Excel that has it |
|---|---|
SUM, VLOOKUP, HLOOKUP, INDEX, MATCH, ROUND | Excel 97 |
SUMIFS, COUNTIFS, IFERROR, COUNTA | Excel 2007 |
IFNA, FORMULATEXT | Excel 2013 |
IFS, SWITCH, TEXTJOIN, CONCAT, MAXIFS | Excel 2019 |
XLOOKUP, XMATCH, LET, FILTER, SORT, UNIQUE | Excel 2021 |
LAMBDA, TEXTSPLIT, TEXTBEFORE, VSTACK, TAKE | Excel 2024 |
GROUPBY, PIVOTBY | Microsoft 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 grid06The @ 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 writes | What 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.
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.
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.
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 of | Write |
|---|---|
=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.
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 grid11Mac, 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 grid12Eight Traps
IFERRORaround a lookup. It cannot tell a missing code from a missing function, and it answers both with your fallback.- Trusting the values in a file you received. They are the sender's last calculation, and they survive only until something recalculates.
- 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.
- Reading
_xlfn.as corruption. The file is intact; the prefix is how Excel keeps your formula safe in a build that cannot run it. - Relying on the Compatibility Checker. It reads the file format, not the version of Excel that is about to open the file.
- Modernising formulas on a schedule that travels. The upgrade that makes your copy clearer is what makes their copy wrong.
- Sending a formula that spills. Older builds do not spill, so nine of ten results have nowhere to land.
- Assuming Microsoft 365 is one version. Channels differ by months, and the semi-annual channel is what most locked-down fleets run.
13Five Exercises
- Reproduce it. Price ten lines with
=IFERROR(XLOOKUP(A2,Codes,Rates),0), then misspellXLOOKUPasXLOOKUPXand read the total. Those zeros are what Excel 2016 computes. - Make it loud. Swap the wrapper for
IFNAand watch the same ten lines return #NAME? instead of a price. - Audit it. Fill
=ISNUMBER(SEARCH("XLOOKUP",FORMULATEXT(C2)))down the rate column and count the TRUEs withSUMPRODUCT. - Translate it. Rewrite each flagged cell as
=INDEX(Rates,MATCH(A2,Codes,0))and check that every line still prices the same. - 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.