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?
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?
- 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.
