Forum Discussion

jkwilliamson333's avatar
jkwilliamson333
Copper Contributor
Aug 02, 2024

xlookup with table issue

Hello!

 

I am working on a file that requires use of xlookup. to keep things organized and for ease of reviewal, i use the table headers in the formulas (instead of just column letter). I realized that my formulas are not working properly. when I set the formula up, it only seems to return the value that appears first in the lookup array.

 

For example, I am trying to find and return three different things: call them A, B, and C. In the table that I am doing the lookup from, if A, B, and C are listed in that order, then my lookups will only have one that works, and it will return A. During trouble shooting, I deleted the rows that had A and B, leaving only C. Once I did this, the C was the first lookup value in the lookup table, and the formula worked (it returned C). This informs me that it is not an error of difference of spelling / spaces / etc. in lookup value and items in lookup array. 

 

Also during troubleshooting, I used the column letters as opposed to the table header names in the formula, and it worked perfect. 

 

Has anyone experienced this before / does anyone have any tips??? 

    • jkwilliamson333's avatar
      jkwilliamson333
      Copper Contributor

      SergeiBaklan Thank you for your response. I do know that about xlookup. I am saying that the next xlookup that is looking up a different value, doesn't work. 

       

      For example below: 

      • in the lookup array: the first row of data is the row with "A", the second row of data is the row with "B"
      • example formulas and output:
        • xlookup("A",[lookup array], [return array]) --> output = A
        • xlookup("B",[lookup array], [return array]) --> output = error

      after deleting the "A" from the lookup array 

      • xlookup("A",[lookup array], [return array]) --> output = error (understandably)
      • xlookup("B",[lookup array], [return array]) --> output = B

       

      Does this make sense?

      • jkwilliamson333's avatar
        jkwilliamson333
        Copper Contributor
        Also, when i use xlookup with the column letters (as opposed to in the table header format), it works perfectly; all values are returned correctly.

Resources