Forum Discussion
Solved
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 to stay the same. E3:F100 and H3:J00.
Is there a way to do this without having to manually change the cell for each line.
Puzzled Richard.
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 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).
- 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.
