xlookup with table issue

[jkwilliamson333]

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

[#2]

@jkwilliamson333 

XLOOKUP returns first found value.

Could you please share the sample or at least screenshot of what exactly you'd like to do?

[#3]

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

[#4]

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.

[#5]

please post an example of the actual formula you are using. is it possible your are using the "table header" format incorrectly? for example it sounds like you have @[columnHeader] which would return only that line instead of the whole column, but maybe it is something else. hard to say without additional information.