Complex Excel Xlookup values not correctly retrieved by Power Query

[MichaelPercival]

Good Morning,

This might be a very specific quirk, but I have repeatedly found that some complex xlookup formulas I have in an excel spreadsheet are displaying correctly in Excel, but power queries retrieving the values from these formula are retrieving incorrectly. Am I right that Power query is trying to process the formula (and in my cases, incorrectly) , rather than retrieving the displayed valuein excel, and if so, is there a setting to make it retrieve just the value?

(I appreciate the below isn't very enlightening, but it appears that the power query is trying to process the formula in the cell, only incorrectly:  retrieving the first 'top-down' result from the look-up table, whereas the formula has 'last to first' so excel is correctly retrieving the last result).

[rajendraongole1 · Accepted solution]

Hi @MichaelPercival -You're correct that this issue likely arises because Power Query doesn't retrieve the displayed value of a cell that contains a formula, but rather tries to recompute it based on its internal logic.

Copy the range containing the XLOOKUP formulas.
Paste the values back into the same range using Paste Special > Values Only.
Load this range into Power Query. Since the cells now contain static values, Power Query will retrieve them without reinterpreting the formula.

There’s no setting in Power Query to "force retrieve only the displayed value" without pre-processing the data in Excel.

Hope this helps.

[MichaelPercival]

Thanks for this. Replacing the values isn't a solution to my specific system, but very valuable to understand the issue. Thanks, @rajendraongole1 !

Michael