Forum Discussion
XLOOKUP Not Finding Associated Value After Cleaning Up Data With Power Query
I am trying to find a file that is associated with a link using XLOOKUP. In the source document I am using, the links have hex characters in the URL and the file name listed in the report does not. I...
Sorry, I didn't catch what is the problem with XLOOKUP. You have two URL:s, in each of them you replaced UTF-8 codes %20 (space) and %C2%A0 (non-breaking space) on " " (ASCII space). As result you have two exactly the same texts.
After that you XLOOKUP such text on list of URL:s. XLOOKUP correctly returns first found one.
- That is correct. The problem is that the XLOOKUP is not finding a match, even though they are exactly the same. Using the Dummy Data attachment, imagine Sheet2 cell B2 results in #N/A. If I copy A2 from Table1 tab into Sheet2 cell A2, the XLOOKUP will find the match even though the replaced value is the same. I hope that makes sense
Sorry, I didn't catch, what exactly XLOOKUP can't find? Could you please share example with the text which returns N/A but you expect another result.
Here's an example. In the first screenshot, you have the URL and I'm using XLOOKUP to find the corresponding asset and pull in it's expiration but it is coming up as not found. In the second screenshot, you can see that the asset does exist and it matches one for one.
- First screenshot
- /R89_Énoncé des travaux 1_Guide de mise en œuvre_Centre de cadeaux des Fêtes 2024_32830.pdf
- /R89_Énoncé des travaux 2_Guide de mise en œuvre_Centre de cadeaux des Fêtes 2024_32830.pdf
- Second screenshot
- /R89_Énoncé des travaux 1_Guide de mise en œuvre_Centre de cadeaux des Fêtes 2024_32830.pdf
- /R89_Énoncé des travaux 2_Guide de mise en œuvre_Centre de cadeaux des Fêtes 2024_32830.pdf
- Before running the Power Query
- /R89_%C3%89nonc%C3%A9%20des%20travaux%C2%A01_Guide%20de%20mise%20en%20%C5%93uvre_Centre%20de%20cadeaux%20des%20F%C3%AAtes%C2%A02024_32830.pdf
- /R89_%C3%89nonc%C3%A9%20des%20travaux%C2%A02_Guide%20de%20mise%20en%20%C5%93uvre_Centre%20de%20cadeaux%20des%20F%C3%AAtes%C2%A02024_32830.pdf
- First screenshot
