Forum Discussion
XLOOKUP formula returns #NA even when value exists
I have a txt file which I imported to excel for analysis. I converted the cell format to numbers as well. I am using this formula XLOOKUP(30,J10:J310,A10:A310,-1) to find a value in column A corresponding to value <=30 in column J. However I keep getting either #NA or -1 as output. Does anyone have an idea of how to resolve this ? Thanks in Advance.
2 Replies
AG2015 I'm pretty sure that the values in J10:J310 are still texts. Formatting a text that look like a number doesn't transform it into a number. How did you import them. You should use Power Query or Text-to-columns to make a proper transformation of the txt-file.
Then, the -1 is probably intended to set the match mode to 'exit or next smaller'. The formula you mentioned has a comma missing to achieve that. It should be:
=XLOOKUP(30,J10:J310,A10:A310,,-1)
- Thanks so much Riny. The missing comma was the issue. It works now .
