Confusion about XLOOKUP

[garyj]

Hello..

I have acquired a #VALUE error which is showing up as a data type error. But I don't understand. I tried simplifying the table and making all data types the same... all numbers, and still it throws an error. I recognize that it could be a column length issue, but in the case of my example with no missing data, the error still exists.

Here is a pic of my example. See formula in formula bar.

What am I doing wrong?

Gary

31206

[Aussiebear]

Lookup requires the lookup value, the lookup array, the return array, [Match mode], [Search mode], with the last two optional. So your function as indicated cannot work. Then there's the logic of your function.... very questionable. Why are you trying to find cell M2 value if it needs to match cell N2?

[p45cal]

It's difficult to know what results you do want when presented with something that doesn't do what you want.
So what results are you expecting?
A guess, in cell P2:

=XLOOKUP(M2,$M$2:$M$5,$N$2:$N$5,"")

[Paul_Hossler]

Just a guess as to what I think you're looking for, but the formulas in col P are all the same

Since they're all lookin to XMATCH K2, they all return the same value


31210


Otherwise it might be somnething like this

31211

[garyj]

It's difficult to know what results you do want when presented with something that doesn't do what you want.
So what results are you expecting?
A guess, in cell P2:

=XLOOKUP(M2,$M$2:$M$5,$N$2:$N$5,"")


Yes, I thought I was making it clear, but when you don't fully comprehend the function, then it's not always easy to know how to ask the question. Anyway, you all helped clear it up. Thanks.

To clarify... I didn't present the actual need because I would have had to show you two spreadsheets and the formula I had and how it wasn't working. I was tired from several hours of trying to figure it out and wanted to get the question out there. So to save time I simply created a sample sheet with a column looking for information in the column beside it (rather than in the other sheet). The actual use is to find something in the next spreadsheet.

Where I erred was in thinking that the entire array was required for the lookup array. When I narrowed it to just the column, it worked.

So thanks for that clarification, and hopefully I have cleared my question up for you.

Gary