To do this, we follow the same approach as with the previous example, but ensure that the symbol is inside the first square bracket. Because of this, the previous cell reference of $E2 has been changed to we need to make this absolute, or it will move to when we copy the formula to the right. In this example, the table on the right that contains the XLOOKUP is formatted as a table. Now, let’s look at how we would make a single table cell reference absolute. The following image shows the formula in column G with the table column reference unchanged. So, the column header is repeated either side of the colon and an extra set of square brackets is added to enclose this range. The complete XLOOKUP formula now looks like this. To do this, we change the staff reference to staff:]. The staff column however, can be left relative, so that it moves to the Department column when the formula is copied. In this example, we need to make the staff column absolute, so that when it is copied into column G to the right, it does not change. It does not work when copied into column G. We have used the following XLOOKUP function in cell F2 to return the Name and Department for each ID from the table on the left, named staff. Let’s look at an example where this behaviour is not desirable. However, when this formula is filled to the right for English and Art. It looks like an absolute reference as it explicitly reads Grades table and the Maths column – Grades. In the following example, the AVERAGE function has been used in cell F3 to average the scores for Maths. This is a surprise to many users as a table reference looks absolute. By default, table references are relative, so they change.
0 kommentar(er)
