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?

CodePudding user response：

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.