Configure Autoformat Settings

Copper Contributor

I am having trouble with the autoformat feature in Excel and I wanted to make sure if this is even a feature.

 

I am using the XLOOKUP function in a formula. This formula first looks at the data in the cell to its left, checks to see if there is a match in a table, and if there is, it sends the data of the cell to its left from the table. This formula operates in a column of cells. How do I get the autoformat to change the cell to its left (going down one row each time) without modifying the range that it looks for in the table?

 

For instance.

XLOOKUP(G3,B3:B8,C3:C8)

 

What I'm asking for is a way for the first part of the function (G3) to be autoformated, without it affecting the rest.

3 Replies

@MrSandMan 

To "fix" a cell reference, place $ before the column letter and row number:

 

=XLOOKUP(G3,$B$3:$B$8,$C$3:$C$8)

 

The official terminology is that the reference G3 is relative, i.e. changeable, while $B$3:$B$8 and $C$3:$C$8 are absolute, i.e. static.

@MrSandMan 

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!