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...
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
Please check in attached if I correctly understood your screenshot (better to have sample file)
Source
Power Query result
XLOOKUP on text from your post
SergeiBaklan Thank you for your reply and assistance so far. I looked at the dummy data spreadsheet you sent over and I'm not sure how that helps with the issue of %C2%A0 vs %20.
- First screenshot
