XLOOKUP in Table: formula problem

[Ferdib]

Hello everyone,

Situation:
On Sheet 1 I have a table called "Table1" - with Column A listing product Serial Numbers.
On Sheet 2 I have Column A with Product Serial Numbers.
On Sheet 2 I have Column B where I want to pull data from Table1 (Column B) with an XLOOKUP.

My working formula looks like this:

=XLOOKUP(A2;Table1[Serial Numbers];Table1[Address];"Unknown Address")

However, when typing the formula I click the header cell of Table 1:
=XLOOKUP(A2; *click Table1 Column A header* results in:

=XLOOKUP(A2;Table1[[#Headers];[Serial Numbers]]

...which does not result in a correct XLOOKUP.

Avoiding the table header and clicking A2 results in:

=XLOOKUP(G3;Sheet1!A2

And clicking the whole column:

=XLOOKUP(G3;Sheet1!A:A

Question: am I doing something wrong?

Thanks in advance!
Ferdi

 

[Peter_SSs]

Question: am I doing something wrong?

If you want Excel to include the table 1 columns as you have shown in the first formula then you have to either ..
- type them as you have shown in that formula, or
- while building the formula, select all the cells in the table column starting from the cell below the header down to the bottom of the table.

 

[Ferdib]

If you want Excel to include the table 1 columns as you have shown in the first formula then you have to either ..
- type them as you have shown in that formula, or
- while building the formula, select all the cells in the table column starting from the cell below the header down to the bottom of the table.

Thanks Peter!
The 2nd tip was what I was looking for!
It just seems logic to me to select the header cell only, as it is referring to the table already... might just be me haha
Thanks again!

 

[Peter_SSs]

You're welcome. Glad to help.

It just seems logic to me to select the header cell only, as it is referring to the table already...

Yes, but if you only click one cell how would Excel know that you wanted to use the column below, and not at the individual heading cell or in fact all of the heading cells?