Data appears differently when referencing cell vs XLOOKUP

Question

I have a data set imported from PowerBI into an Excel sheet via a PivotTable. I am trying to grab some of this data based on user input by using XLOOKUP function.

data:

1|    A   |   B    |
2| 112272 | 64'442 |

lookup: =XLOOKUP(UserInput;A:A;B:B)

If UserInput is 112272 I should receive the value of 64'442. Instead I am receiving 64442.1328 If I change the lookup cell to a direct reference =B2 I receive the expected 64'442. This happens within the same cell, so I don't believe it's a formatting issue.

Why are results different when direct referencing a cell vs getting data via XLOOKUP?

EDIT: If I get the data as a direct reference to =B2 and then copy it's format to the cell with the lookup the value indeed is show correctly, so it is just a formatting issue.

If I replace the content of the cell containing the lookup with a direct reference, the value is shown incorrectly.

So the question remains, why does XLOOKUP "generate" some different formatting than a direct reference does?

@Harun24hr the value I see inside the cell is 64’442. If I copy and paste that cell into a text editor I receive the same value. — Claude Hasler, Aug 30 '22 at 08:59
Copy the B2 cell then paste to another cell as values only. They you will see the actual value. — Harun24hr, Aug 30 '22 at 09:19
@Harun24hr this results in 64442.1328125. So the lookup is returning the correct value. The question remains: why does the lookup result format itself differently than the direct reference? — Claude Hasler, Aug 30 '22 at 09:36
@ClaudeHasler Excel function always return values only until you use formatting function. You may use `=INT(XLOOKUP(UserInput;A:A;B:B))` or `=TEXT(INT(XLOOKUP(UserInput;A:A;B:B));A:A;B:B),"#,#")`. — Harun24hr, Aug 30 '22 at 09:57

score 1 · Accepted Answer · answered Aug 30 '22 at 09:56

When you directly reference a cell, Excel assumes you want the same number formatting, with the Xlookup it doesn't do this as it is referencing multiple cells which may have different formats. as Harun 24hr has suggested pasting as values should show you the actual value.

Data appears differently when referencing cell vs XLOOKUP

1 Answers1