Forum Discussion
Configure Autoformat Settings
Some further thoughts. If you are using XLOOKUP you will be using Excel 365. That has dynamic array functionality that makes you far less dependent on the concept of relative referencing than you would be if you were tied to tradition Excel.
The idea of relative referencing is that it is easy to create a set of formulas that apply to each record in a list or, similarly, each element of an array, by using fill down. The user may have little idea even of what an array is but still be able to access their 'numbers'. With the coming of Excel 365, it is now practical to work almost entirely with arrays defined by absolute referencing. In your example you could write the formula
= XLOOKUP($G$3:$G$5, $B$3:$B$8, $C$3:$C$8)
in a single cell ($H$3 say) and the result would occupy the 3-cell range $H$3:$H$5.
Better still, is to hide the grotesque direct referencing and use named ranges
= XLOOKUP(lookup_value, lookup_array, return_array)
[names specific to your business domain would be better again]
If you use Excel Tables to store source data, the column headers provide a default set of names for the data fields without further effort on behalf of the user and these ranges adjust to match the input data.
- Thank you!
