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?

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?

• google-sheets
• google-sheets-formula
• xlookup

• 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 Commented 12 hours ago

1 Answer 1

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