How do I apply ARRAY Formula for XLOOKUP?

Question

I have an XLOOKUP function that is working the way I want. (I've wrapped it with some validation functions)

=IF($A$2:$A<>"",IFNA(XLOOKUP($A2 & B$1, ARRAYFORMULA('Unit Update'!$B$2:$B & 'Unit Update'!$D$2:$D), 'Unit Update'!$A$2:$A), ""),"")

I want to apply an ARRAY FORMULA so that I do not have to drag the fill handle down every time I want to update this, because this formula is intended to see on-going use for a regularly expanding list. My current, very limited understanding is that I just need to wrap the whole thing in ARRAYFORMULA() like below

=ARRAYFORMULA(IF($A$2:$A<>"",IFNA(XLOOKUP($A2 & B$1, ARRAYFORMULA('Unit Update'!$B$2:$B & 'Unit Update'!$D$2:$D), 'Unit Update'!$A$2:$A), ""),""))

But when I do so it only repeats the first value rather than applying the formula how I want it to be applied. Here is an image of the first XLOOKUP function, without ARRAYFORMULA, when I just drag out the fill handle: XLOOKUP NO ARRAYFORMULA

And here is an image of the second XLOOKUP within ARRAYFORMULA: XLOOKUP WITH ARRAY FORMULA

I have no clue as to why. I'm hoping it's just something very small that I missed. Is there a better way to accomplish what I am trying to do?

Please do not upload images of code/data/errors. Instead, provide a minimal, reproducible example using Tables or tools like Table to Markdown that can easily be copied and pasted. If necessary, as a last resort, Make an anonymous sample document. — Saddles
– Saddles, Commented Apr 3 at 19:02

rockinfreakshow · Accepted Answer · 2025-04-03 19:08:56Z

2

Change this specific part of the formula

XLOOKUP($A2 & B$1,

to

XLOOKUP($A2:A & B$1,

so that entire range of A2:A is considered

answered Apr 3 at 19:08

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I apply ARRAY Formula for XLOOKUP?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related