=VLOOKUP(A2;$Sheet1.A2:Sheet1.B1000;2)
works. Could I obtain the same for the entire column with an Array function? With the additional benefit casual users, even more casual than me that is, won’t know how to modify it.
Seems XLOOKUP now available in v24.8 dev may do what I want. But I am stuck with v7.4 anyway.
Side note: why XLOOKUP references COM.MICROSOFT.XLOOKUP ?
I don’t understand what your problem is. Formula
{=VLOOKUP(A2:A1000;Sheet1.$A$2:$B$1000;2;0)}
will fill column B with the desired results.
{=LOOKUP(A1:A100;$Sheet1.A1:A100;Sheet1.B1:B100)}
is also exactly what I need. Thanks.
Because that function isn’t part of ODF, and was added to Calc for compatibility with Excel, so it is defined by MS (not a function that we can define behavior ourselves). Or what is your actual question?
You answered my question already. But also are not functions on most used spreadsheet programs re-implementing each other most used functions? If true, why this one has to be explicit about it? Just curious.
Do you think that XLOOKUP was the only one doing that, and that is why you try to combine the two unrelated questions - “are not functions on most used spreadsheet programs re-implementing each other most used functions”, and “why this one has to be explicit about it” - into one? All MS-specific extensions use this namespace. Also, there are OOo-specific extensions, LO-specific extensions, …
I was. No more questions.
Yes, you’re right - in most cases this formula will return the correct result. But please make sure that the source data on Sheet1 is always sorted correctly.
Now that you mentioned I did notice some odd results. Why is sorting relevant? Doesn’t look for an exact match?
Interesting. On mobile so cannot check it right away: nesting SORT(A1:A100) is the solution, right?
sorry NO! it isnt the solution.