Is it possible to spill only parts of a formula?

Hi community, I have a formula that i want to spill, but i want parts of it to stay the same. take the formula =XLOOKUP(A3,E3:E100,I3:I100). I want to spill the cell A3, but i want the two arrays ...

excel

Formulas and Functions

Riny_van_Eekelen
Sep 26, 2022
Tinny426 Don't know what you mean by wanting to "spill A3", but perhaps your intention is to fix the references to the lookup_array and the return_array, allowing you to drag the formula down.

Try this then:
=XLOOKUP(A3,$E$3:$E$100,$I$3:$I$100)
But, perhaps better to use named ranges for the two arrays. Then you avoid the use of direct cell references with the $ signs to make them absolute (i.e. fixed by row and column).

Riny_van_Eekelen

Platinum Contributor

Sep 26, 2022

Tinny426 Don't know what you mean by wanting to "spill A3", but perhaps your intention is to fix the references to the lookup_array and the return_array, allowing you to drag the formula down.

Try this then:

=XLOOKUP(A3,$E$3:$E$100,$I$3:$I$100)

But, perhaps better to use named ranges for the two arrays. Then you avoid the use of direct cell references with the $ signs to make them absolute (i.e. fixed by row and column).

Tinny426
Copper Contributor
Sep 26, 2022
Hi Riny, Im glad you worked out what i was asking, because you have sorted out my problem perfectly! I love learning new things about excel.
Thanks Richard.

Forum Discussion

Is it possible to spill only parts of a formula?

Resources