Forum Discussion
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???
XLOOKUP returns first found value.
Could you please share the sample or at least screenshot of what exactly you'd like to do?
- jkwilliamson333Copper 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?
- jkwilliamson333Copper ContributorAlso, when i use xlookup with the column letters (as opposed to in the table header format), it works perfectly; all values are returned correctly.