Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 26, 2024

renee_crozier 

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.

renee_crozier's avatar
renee_crozier
Brass Contributor
Oct 28, 2024
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
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 28, 2024

    renee_crozier 

    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.

    • renee_crozier's avatar
      renee_crozier
      Brass Contributor
      Oct 29, 2024

      SergeiBaklan 

       

      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
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 29, 2024

        renee_crozier 

        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

Resources