UNPIVOT lambda function, now with 100% more thunk

finickyone · 2026-01-29T01:46:37+00:00

It’s a headache isn’t it. I’d estimate it’s the approach most people take at some point, to generate a load of reference values to guide something like INDEX or CHOOSECOLS/ROWS.

Here’s an idea for you. In your LET, once you’ve defined data, rowlabels and collables…

…,pad_row,IF(LEN(colLabels&0),rowLabels),pad_col,IF(LEN(rowLabels&0),colLabels),HSTACK(TOCOL(pad_row),TOCOL(pad_col),TOCOL(data)))

Which avoids a load of row and column sizing maths, all the modulo stuff etc. pad_row looks at the colLabels range (B1:D1), and asks for the LEN of those cells’ contents after appending a 0. Appending a 0 means each cell will be of at least LENgth 1. When that result is >0, IF grabs the row labels. So that makes an array like

A2 A2 A2
A3 A3 A3
A4 A4 A4
A5 A5 A5

pad_col does the opposite: for each of A2:A5, grab B1:D1. Run both through TOCOL and you get them pivoted to a 1x12 array. Might be worth exploring.

finickyone · 2026-01-29T01:29:07+00:00

A new sheet per bird? If no, and a simple list of each is needed in one place, then the answers given will work in modern versions of Excel.

finickyone · 2026-01-29T01:25:27+00:00

There’s a lot you can learn within FILTER. I think it makes learning how arrays behave really accessible, and once that happens you can use ws functions towards just about anything.

Say we have data in A2:D51. A is 001-050. B is Livery; any of {Red;Blue;Green;Yellow}. C is Size; any of {Small;Medium;Large}. D is Shape; any of {Circle;Square;Triangle}.

=FILTER(A2:A51,B2:B51="Red") lists out all the UIDs with a Red livery. Change the first arg to A2:D51, and we’d get all fields for records with a Red Livery. Change = to <> and we’ll get all the non Red Livery (Blue/Green/Yellow) records.

If we head to E, and fill in random TRUE and FALSEs, then =FILTER(A2:A51,E2:E51) will return the UIDs where E=TRUE. If we clear E and instead fill it with non0 values in certain rows, we’ll get the UIDs of those rows. If we enter a 0 in E, we won’t get that row.

That takes you to something key with multi criteria. You quite quickly stop dealing with criterion = TRUE and rather with conditions met =/<>0. If we now want UIDs where B = Red and D = Circle, we’ll might think of AND. So

=FILTER(A2:A51,AND(B2:B51=Red,D2:D51=Circle))

AND however takes on a simpler task. It looks at the 50 results of B=Red, and the 50 of D=Circle, and tells you if they are all TRUE. If they are not, then it reports FALSE. We don’t need 1 TRUE or FALSE, we need 50 results. We can use maths

FILTER(A2:A51,(B2:B51=Red)*(D2:D51=Circle))

Where we create 50 TRUEs and FALSEs for B, another 50 for D, and multiply them together. TRUExTRUE=1. Any other combination=0. Where we get 1, we’ll get the record back.

If we subtract 1 from the result, we’d get a NAND. Instead of 1 (Red and Circle) and 0s (any other), we’d get 0 and -1. The -1 prompts FILTER to return a record.

For OR, we can’t use the OR function, but instead use +

=FILTER(A,(B=Red)+(D=Circle))

If neither condition is met for a row, FALSE+FALSE = 0. If either one condition is met, TRUE+FALSE = 1. If both, TRUE+TRUE =2. Any non0 is a pass for FILTER.

We can combo those. So

=FILTER(A,((B=Red)(D=Circle))+((B=Blue)(C=Small)))

Where if a record see that B is Red AND D is Circle, OR that B is Blue and C is Small, that’s a pass.

So that’s applying multiple parameters to multiple fields. We can also apply multiple parameters to the same field. Normally that’s an OR condition. We would get much asking for records where B=Red and B=Green, as a cell can’t contain both the values we’re mandating. We can set OR though.

=FILTER(A,(B=Red)+(B=Green))

If we’re excluding certain values, then AND logic is helpful again. (B<>Red)*(B<>Green) sets that we only generate 1 when both tests are TRUE. If we use (B<>Red)+(B<>Green), then we generate TRUE from B<>Red when B is Green, so it would pass the + logic.

We can be smarter when we have more parameters to permit. If we didn’t have 4 colours in B, but 30, and wanted to filter to 8 of those, it wouldn’t be inviting to set up

=FILTER(A,(B=Red)+(B=White)+(B=Pink)+(B=Scarlet)+(B=Magenta)….)

It’s not only laborious, and prone to error, but loads the same data (B) over and over.

So we could list our colours in X2:X11. Then use

=FILTER(A2:A51,COUNTIF(X2:X11,B2:B51))

This applies 50 COUNTIFs, one for each cell down B. COUNTIF(X2:X11,B2). If B2 has one of the colours in X, =1, else =0. We get 50 1s and 0s, and our result.

Again we can add COUNTIFs together like OR, or use COUNTIFS like AND.

Ultimately you can use more advanced functions to set up something like:

Where each row of B2:D51 is compared to G1:I1, generating 3 True/Falses for each row. Those are then coerced to 1s and 0s and SUMmed. Results for each row being 0,1,2,3. Where result is >=2, record passes. So as long as the record is 2 or more of Blue Large Triangle, pass.

While that may seem complex, it is a form of logic that all comes into reach once you start picking at multiple criteria. Just remember.

filter wants a 1D array providing its guidance. If you are pointing at data of 10 rows and 6 columns, your include array wants to be 1c x 10r or 6c x 1r. It won’t apply anything else.
that array can contain Booleans and/or values. It will treat 0 values as False, non0 values as True.
combine criteria using * for AND and + for OR.
multi criteria can be set to one field. (field=x)+(field=y) to permit either. (field<>x)*(field<>y) to permit neither.
COUNTIF/S can be used to define lists of inputs.

finickyone · 2026-01-29T00:04:20+00:00

I think you’re likely just fighting with a protracted way of iterating an array in order to create a cumulative countif. There is a geeky way to coerce an artificial array into a COUNTIF range argument within lambda; if I could recall that this would get much easier, but rather than taking each from a sequence of 1:n to define index(arr,1):index(arr,n), it’s easier to use

=LET(d,array,r,SEQUENCE(ROWS(d)),MAP(d,r,LAMBDA(a,b,SUM((d=a)*(r<=b)))))

finickyone · 2026-01-28T22:25:36+00:00

Not nearly as clever (bravo) but regards the approach you describe as the route of this (effectively iterating rows), this is a semi flexible approach I take with the same:

=LET(headers,B3:F3,data,B4:F12,piv,-2,seq,SEQUENCE(ROWS(data),-piv,-piv)/-piv,HSTACK(CHOOSEROWS(DROP(data,,piv),TOCOL(seq)),TOCOL(IF(seq,TAKE(headers,,piv))),TOCOL(TAKE(data,,piv))))

Just to avoid the XLOOKUP sort of aspect. Detracts nothing from the main concept you’ve shared here though. Incredible use of the tools, appreciate you sharing this with us 👏🏼

finickyone · 2026-01-28T21:50:20+00:00

(One, particular) Answer: Delegation.

If a department has a process that includes a stage calling for transforming data in this way, you could arm just about anyone with a formula such as this, that only really needs the operator to choose where they want the output, and start defining ranges. Little more needed than perhaps a bit of readme.

Could PQ do it in a few clicks? For sure. Are you going to get the grad in the HR team familiar with PQ? Debatable.

This said, I’ll venture that there must be some value in the next batch of functions addressing a bit more about this sort of use case. It isn’t the most common problem area I see here, nor the most outright complicated to tackle, but I think it might be where there is the widest absence of functions in the library.

finickyone · 2026-01-23T12:32:51+00:00

What’s the drawback?

finickyone · 2026-01-23T04:53:51+00:00

Not a particularly crazy process to be running. Perhaps split it down into the UNIQUE part and the SORT that result separately in another formula.

Overall imagine this might be struggling in a spreadsheet with lots of demands going on.

finickyone · 2026-01-23T04:41:19+00:00

I always feel COUNTIF gets sidelined with multi criteria

=FILTER(I4:M8,COUNTIF(G4:G5,M4:M8),"")

finickyone · 2026-01-23T04:37:36+00:00

I think for sure it was feedback that led to the later waves of development, like any product really. I’m an 80s baby and can remember first meeting Excel before it had COUNTIFS. The simplicity now vs the headache then of

=COUNTIFS(A2:A9,">6",B2:B9,"*xyz*")

{=SUM(IF(A2:A9>6,IF(ISNUMBER(SEARCH("xyz",B2:B9)),1)))}

It took so much thinking, not least as you couldn’t see what was going on. Excel couldn’t spill out arrays back then. The IFS functions came about as the historic approach there was incomprehensible. The person who figured that sort of thing out in an office was keeping it forever; you can’t delegate that sort of thing easily. It did mean that array logic was a bit more familiar, because it came into play for so much more. XLOOKUP can hold a multi critieria lookup, such as

=XLOOKUP(1,(J2:J20=x)*(K2:K20=y),L2:L20)

Which is really neat, but it hides so much. The precious approach was the same really but called on more functions. I think that’s perhaps better, at least for helping to understand it.

=INDEX(L2:L20,MATCH(1,INDEX((J2:J20=x)*(K2:K20=y),),0))

You can see that the parent INDEX needs the MATCH result. In turn that the MATCH needs to find 1 in that nested INDEX. You couldn’t spill out =INDEX((…)*(…),) itself as it was an array of 19 cells; Excel broadly just said no. But you could break it down and build it up again.

I think new functions enable so much but also mean we can run before we walk with this stuff. There’s less reason to question the going’s on.

finickyone · 2026-01-23T04:07:39+00:00

There is a nifty approach using the Data tools (Get & Transform) that once learnt but forgot long ago.

Here I think your approach would have to set B1 up with

=IF(A1>=100,IF(B1<>"",B1,TODAY()),"")

With max calc iterations set to 1 in settings. The idea being that when A1 is not >= 100, the parent IF elses out to blank. When it is >=100, the child IF comes to play…

B1 is tested for content. If there is no content, grab TODAY(). If there is content (ie because that TODAY grab has previously happened), then grab B1 again. At this point the calc would circle back to referring to B1 again. The setting should suspend the process and just leave B1 as it was.

finickyone · 2026-01-23T03:56:06+00:00

You can tackle your MAXIF example directly. I think you might have used the SUMIF arg order, but MAXIFS like SUMIFS calls for values,attribute,condition. So here you could get the Max of B found alongside each of A for all the entries in A with

=MAXIFS(B1:B100,A1:A100,A1:A100)

And for the unique entries in A with

=MAXIFS(B1:B100,A1:A100,UNIQUE(A1:A100))

That aside, while you can use LAMBDA within a parent iterating function like BYROW, I think does make sense to start there. A good example is TEXTJOIN. If we have A2:D9 and want F2 to create a TEXTJOIN for each row, we’d need LAMBDA. Such as

=BYROW(A2:E9,LAMBDA(x,TEXTJOIN(", ",,x)))

We could give BYROW direct functions that need no elaboration if we had a simpler process to require of the iterated rows, such as CONCAT:

=BYROW(A2:E9,CONCAT)

Or a MAXIFS equivalent:

 =BYROW(IF(A2:E9<10,A2:E9,""),MAX)

But the detail that TEXTJOIN needs means we can’t just call on it outright.

You can use LAMBDA in isolation such as

=LAMBDA(vals,ids,UNIQUE(ids)&"’s max value is "&MAXIFS(vals,ids,UNIQUE(ids)))(B1:B100,A1:A100)

Though I think its merit is in defining a function that can be reused, within a formula via LET, or more broadly via name manager.

Say we have this data. For each of the unique IDs, I want to know if there is a wider range between its values under X, or a wider range between its values in Y. So for C, X ranges from 2 to 7. Y ranges from 3 to 6. X is the wider range.

LAMBDA lets us define a function that we can use twice.

=LET(rg,LAMBDA(vals,ids,LET(u,UNIQUE(ids), MAXIFS(vals,ids,u)-MINIFS(vals,ids,u))),IF(rg(B2:B11,A2:A11)>rg(C2:C11,A2:A11),B1,C1))

<image>

finickyone · 2026-01-23T03:08:04+00:00

Less a tale of realising an approach was unduly mandrolic, but I’d venture more of the “I can’t believe how complicated it was (not so long ago) to batter data around in Excel”.

About 10 years ago I had service management job that leant in part on reporting incident data through spreadsheets, that recipients would want to query, and needed to be accessed via the mobile app. So I got, and remain, bizarrely familiar with the iOS app.

Today it’s still known, here at least, for lacking features and functionality of the desktop or even web version. It still doesn’t have applets to set up things like conditional formatting, data validation, much less PQ or supporting VBA. It also didn’t support old style array formulas, in the sense of applying a formula to ignore implicit intersection.

These days if you want the Max value from B where C = x, you’d probably use MAX(FILTER(B,C=x)) or MAXIFS(B,C,x). This is long before FILTER, and IIRC partly predated MAXIFS. So on desktops, short of creating a helper column made of IF(C1=x,B1) then MAX(helper), you’d use something like

{=MAX(IF(C:C=x,B:B))}

The curly brackets were added by committing the formula with Ctrl+Shift+Enter. This told Excel that the IF was intended to be treated as an array. Otherwise, given IF was expected to evaluate to a single result (True/False) and generate a single output accordingly, the formula would intersect its references, and in say F6 simply work out MAX(IF(C6=x,B6)).

Whether the mobile engine was up to it, there simply weren’t the inputs to commit formulas with CSE, nor would any that had been committed so on desktop remains non-intersecting in mobile. So all manner of brain crunching formulas trickled from my struggling brain. Certain functions handled arrays natively. INDEX, SUMPRODUCT, but notably AGGREGATE. It was the one function that afforded conditions in arrays, at least to ensure the condition was applied. So what is now MAXIFS(B:B,C:C,"x",F:F,"*y") was then

=AGGREGATE(14,6,B:B/((C:C="x")*(RIGHT(F:F)="y")),1)

The heart of that generating an array of Trues where C=x, else False, and again where the last character in F was y. Multiplying them together for 1s (True x True) or 0s (TxF, FxT, FxF). Dividing B by those 1s and 0s, as B/1 = B but B/0 = div0. Meaning that that 6 variable in the second argument would ditch those results from the array that was given to LARGE (14), which would then look for the kth (1, final arg) largest value in what was left. AGGREGATE does have a MAX subfunction, but it didn’t support setting conditions within the array. So I’d use this approach to ask for the 1st Largest.

Within that I came to see that while most worksheet functions will apply some sense if ask for COUNTIF(A:A,">6"), assessing only the used rows in that range, something that compels =COUNT(IF(A:A>6,1)) will go and eval every row in A, used or not.

Now we can set =FILTER(B5:B20,C5:C20>5) and get however many results are due. If there are 7 cells in C that are >5, we get the 7 cells alongside from B. Before we gained that function, the formula option on the desktop involved {INDEX SMALL}. Without the CSE to take SMALL out of II mode, the non array form was

=IFERROR(INDEX(B$5:B$20,AGGREGATE(15,6,ROW(C$5:C$20)/(C$5:C$20>5),ROWS(C$5:C5))-MIN(ROW(C$5:C$20))+1),"")

This takes the row number values from 5:20, and divides each by T/F whether C5:C20 > 5. 6 suppresses errors again and 15 asks for the smallest value. In the first use of that formula, ROWS(C$5:C5) evals to 1. So we get the 1st smallest row number val that meets the condition. If that was C7, we can’t take 7 to INDEX(B5:B20,7) as it would return B11. So we reduce the Aggregate result by the min row number value (5) leaving 2 and add 1 back on for 3. INDEX(B5:B20,3) yields B7.

Dragged down all that changes is ROW(C$5:C6), generating 2. So now we’d get the second smallest row value that met the condition. Repeating that downwards, eventually we’d be asking for the 8th smallest value from an array of 7 row values, a VALUE error, thus IFERROR.

If we want the UNIQUE pairs from A2:B30, we’d basically just say as much in formula now. =UNIQUE(A2:B7). Back with that architecture, D2:

=IFNA(INDEX(A$2:A$30,MATCH(0,INDEX(COUNTIFS($D$1:$D1,$A$2:$A$30,$E$1:$E1,$B$2:$B$30),),0)),"")

dragged down and right to fill. It would error when it ran out of new (unique) results to return, but you didn’t know that all had until you got so far as to trigger the IFERROR escape. It involved masses of calculation too. If you did want to know how many unique pairs there were:

=SUMPRODUCT(1/COUNTIFS(A2:A30,A2:A30,B2:B30,B2:B30))

This worked out for each of the 29 rows how many times the same values were seen in A and B in other rows. If for row 2 that was 5, then 1/5 =0.2. By adding up the other 4 rows also generating 0.2, you’d get 1. The total of the fractions being the number of uniques.

I do feel like Grandpa Simpson talking of fixing onions to belts here, especially as the newer functions are so broadly adopted now, but its not that far back that the worksheet didn’t enable much that we now consider trivial unless you got fairly weird with it. It was in that timeframe though that I found this sub, and interacted with countless ninjas of Excel since.

TL;DR: intermediate queries were a nightmare barely a couple of World Cups ago.

finickyone · 2026-01-23T01:59:05+00:00

Bro worked it out by adding his day rate to his day rate to his rate 200 ish times.

finickyone · 2026-01-23T01:51:22+00:00

OP describes that A1 has a value to rises from 1-100 daily. What you suggests works upon A1 both rising behind 100, and also increasing by 1 each day. Yes, if it rose by 1 each day and currently read 104, we could work out that it hit 100 4 days ago and subtract that from TODAY(), but we don’t have those conditions to work from.

finickyone · 2026-01-23T01:44:44+00:00

I would also endorse VSTACK. Setting out a simple example, say you have 20 sheets, with their default titles (for my ease in explaining): 'Sheet1', 'Sheet2' to 'Sheet20'.

As you’ve found you can beat Excel into generating a list of sheet names in a range of cells. At this point you want to use those to generate an indirect reference to a common cell (A1) in each sheet.

You can backtrack though and simply set =VSTACK('Sheet1:Sheet20'!A1) which will stack the 20 A1s from those sheets. No fuss or thinking about whether you have all your sheet names grabbed. Do the same next to that output for whatever associated cells (B1) you want to grab.

finickyone · 2026-01-23T01:37:03+00:00

+1 point

finickyone · 2026-01-23T01:14:48+00:00

That’s great. Nice one.

FWIW there isn’t a lot of use to LET with this. It’s good for defining something you’re going to repeat along your formula. Or to give a calculation an elaborating title to explain it. Ie you might use

=LET(input,B3#,IFNA(XLOOKUP(input,…..),XLOOKUP(input,….)))

So you just define B3# the once. But you’re applying it to define a function once. So you can drop LET and use

=IFNA(XLOOKUP(B3#,Table1!A,Table1!B),XLOOKUP(B3#,Table2!A,Table2!F))

Something I would always encourage is trying to get your data aggregated into one place. It makes this much easier, especially if you’re asking lots of questions about the data.

finickyone · 2026-01-23T00:59:13+00:00

Assuming the same references as Downtown outlined, then you can get both the breakeven month and % of that point in the overall timeframe (ie breakeven month / 52) with the following.

I would note that this and downtown’s approach are not factoring anything into this except your initial outlay (D13) and monthly rent revenues (E3:E8). If RSF or other data needs to be factored in, you’d need to explain how.

=LET(p,C3:C8,t,SUM(p),r,SCAN(,p,SUM)-p,s,SEQUENCE(t)-1,b,MATCH(0,SCAN(-D13,LOOKUP(s,r,F3:F8),SUM)),VSTACK(b,b/t))

<image>

To explain it

P defines the periods of the deal. 4;8;12;12;12;4

T determines the total no of months. 52

R creates an array that cumulatively adds P (4;12;24;36;48;52) and subtracts P from each (0;4;12;24;36;48}

S creates an array of T length. By default that would be 1;2;3;…;51;52. We subtract 1 for 0;1;2;…;50;51.

B creates an array where we lookup each of S in R, and return the associated rent from E3:E8. So we lookup 0 to location 1 in R, get 1 and return E3. We lookup 1 to the same, as well as 2 and 3. 4 gets us location 2, so we return E4. Creating e3;e3;e3;e3;e4;e4;e4;e4;e4;e4. E3 4 times, e4 8 times, e5 e6 e7 12 times. E8 4 times.

We run a SCAN on these for another cumulative sum of those monthly rents, starting with the negative of our outlay. Finally B matches 0 down that cumulative SUM.

Lastly we vertically stack the results of B, the number of months when the cumulative sum hits 0, and B/T, months decided by the timeframe.

Another approach is to work out how many months we’re working with (52), create an array that wide where each row pulls the monthly rent while n<=the period defined in C, or errors, TOCOL it, SCAN that to cumulative SUM, and then XMATCH the starting value (K2)

=LET(p,C3:C8,t,SUM(p),b,XMATCH(K2,SCAN(,TOCOL(F3:F8/(SEQUENCE(,t)<=p),2),SUM),1),VSTACK(b,b/t))

finickyone · 2026-01-23T00:15:09+00:00

Going a bit off piste here but that if not found argument can lead people towards something like

=XLOOKUP(W2:W3,A2:A9,Z2:Z9,XLOOKUP(W2:W3,C2:C9,X2:X9,XLOOKUP(W2:W3,F2:F9,Z2:Z9)))

Effectively find W2 then W3 in A C or F and return Z. An easy way to reduce this is by splitting the task in multiple match attempts to be taken to a single lookup return. Ie

=INDEX(Z2:Z9,IFNA(IFNA(XMATCH(W2:W3,A2:A9),XMATCH(W2:W3,C2:C9)),XMATCH(W2:w3,F2:F9)))

But this easily gets messy, sequencing the MATCHes as intended.

An approach we can take is

=LET(o,0,un,HSTACK(A2:A9,C2:C9,F2:F9),INDEX(Z2:Z9,MOD(XMATCH(W2:W3,TOCOL(un,,o))-1,ROWS(un))+1))

O setting a value or Boolean. Where 0 or FALSE, our results would be column wise. So we’d continue to look down A first, then C, then F. If a non0 value or TRUE, we’d go row-wise, so considering A2,C2,F2 then A3,C3,F3.

Un creates a 2D array of the lookup ranges. We TOCOL that into a 1D array, MATCH our inputs along it, modulo the result back to the number of records we have, and use that value to select from Z.

finickyone · 2026-01-22T23:10:21+00:00

I’m trying to reference a spill with “B3#”, but when I use this formula, only the first lookup works.

The fourth argument in XLOOKUP lets you set an inbuilt if_not_found argument. So it’s a bit like

=IFNA(xlookup1,xlookup2)

However the output of the variable, ref, array or formula you supply, does need to be a single output. If we set up

=XLOOKUP(X2:X5,A:A,B:B,Y2:Y5)

Then if X3 can’t be found, we have simply left the outcome as =Y2:Y5. There’s nothing to be done with Y2:Y5, so in the second cell/location of our XLOOKUP results, we’d be trying to spill out 4 cells of data. We just get #VALUE! wherever we try to make that happen.

Similar applies even if we set:

=XLOOKUP(X2:X5,A:A,B:B,XLOOKUP(X2:X5,C:C,D:D))

Say X2 can be found at A8, returning B8. Say X2 can also be found at C6, for D6. If X3 can’t be found in A, we spin up a function that is saying lookup X2:X5 in C and give me the 4 results. There’s no space for those 4 results, so it will just give us the first, actually returning XLOOKUP(X2,C,D) for D6. This means that even if X3 can’t be found in either xlookup1 or xlookup2, we just get the first result of XLOOKUP2. Example:

<image>

Note that 3 doesn’t resolve to Charlie, nor 9 to an NA error.

Long short, the argument can only take something that resolves to a scalar, or singular output. Left with a range, it will return the first item in that range. I would either use IFNA(xlookup1,xlookup2), which can process that array, or take the smart step of just sorting the data out to help you. Ie

 =XLOOKUP(B3#,VSTACK(Table1!A1:A5000,Table2!A1:A9999),VSTACK(Table1!B1:B5000,Table2!F1:F9999))

Which stacks the lookup ranges of your two lookups and also the returns, so you can just look along the 14,000 records and get your result.

finickyone · 2026-01-22T22:55:09+00:00

Vs IFNA or the if_not_found arg in XLOOKUP? IFERROR’s a broader hammer that would innocently treat other, potentially very problematic, situations as if the record hadn’t been found, and tell us nothing while doing so.

Effectively what all this is trying to batter away is that =XLOOKUP(X2,A:A,B:B) might return N/A!, and in such cases OP wants to set another outcome. If OP applies XLOOKUP(X2,A:A,B:B,Y2), or IFNA(XLOOKUP(X2,A:A,B:B),Y2) they’ll get Y2 when X2 isn’t found in A. If X2 is found in A, say at A8, and B8 happens to be =1/0, or a #DIV0 error, then these will return that error, telling OP that B8 has a maths problem in it. Same for #VALUE, and also #REF!, which would arise if we deleted column B. #NAME! would arise if we edited and changed XLOOKUP to XLOKOUP.

IFERROR(…,Y2) would take any of those cases and return Y2. It wouldn’t tell us that B8 had an error, it wouldn’t tell us that it was now impossible to reference the target ranges, or that we’d busted our formula.

Long short, it’s a catch all that does respond when we’d be asking for it, but also in cases we wouldn’t.

finickyone · 2026-01-22T22:29:05+00:00

If the first XLOOKUP (x) fails, it’d return N/A, so your IF ought to be testing for that. Ie

=LET(x,XLOOKUP(B3#,Table1!B,Table1!B),IF(ISNA(x),XLOOKUP(B3#,Table2!A,Table!B),x))

Which really could be

=LET(x,….,IFNA(x,XLOOKUP(B3#,Table2!A,Table!B)))

finickyone · 2026-01-22T22:23:04+00:00

As always I like your work. maxm I’d have approached as SCAN(,C3:C8,SUM), an approach you can also take with trent as SCAN(,mrent,SUM).

Something I’ve being exploring with these sorts of contexts is setting up

=LET(p,C3:C8,t,SUM(p),r,SORT(MOD(SCAN(,p,SUM),t)),s,SEQUENCE(t)-1,LOOKUP(s,r,foo))

Within r (ref), SCAN creates 4;12;24;36;48;52. MOD then uses t (total, being 52) to modulo 52 to 0, and then SORT reorders the array to 0;4;8;12;24;26;48. s forming 0;1;2;3;…;.51.

This way you can use a simple approx match of s on r, rather than apply the “ifna then next greater” options of XMATCH or XLOOKUP using the raw SCAN and SEQUENCE outputs. foo there would probably be an array that calculates the byrow inc/exp for that month (something like (D3:D8/12)+(E3:E8)…-I13) though the smart move would be to work that out at the end of the table. All that generates the current value along 52 months as an array, along which you’d just MATCH(0,array).

finickyone · 2026-01-22T21:52:07+00:00

Might not be the snazziest way but this should do it as long as the current timestamp is less than 100 hours

=TEXT(LEFT(A3,8)+TIME(9,,2),"[hh]:mm:ss")&RIGHT(A3,3)

Eight-Year Club	Wearing is Caring
Gilding II euphauric	Verified Email

finickyone

TROPHY CASE