Calc: vlookup gives Err.502 though I'm sure the syntax is correct

I have a list of values on column A in column B I want to apply a VLOOKUP with the search value in A and the array in another sheet and give me index 3. I use the following formula: =VLOOKUP($A1,Sheet1.$A$2:$A$27,3,0)

Somehow this gives me Err.502

I don’t understand what I am doing wrong.

Also, if I change the formula to look on index 1 it gives me the same value of cell A1 .

I get “Err:502” when trying to VLOOKUP a value in a different spreadsheet.

My formula is “=VLOOKUP(‘file:///home/leon/background information/Journey to Work/2011 Walkscore correlations by ACT suburb.ods’#$Summary.A9:N112,A10,2)”

My syntax is correct. If I change the index from 2 to 1, the result is my lookup value (the same value as in cell A10 in the current sheet).

You want to return the third column but the range has only one column. Make that Sheet1.$A$2:$C$27 instead. The lookup is always on the first column of the given range, the index argument indicates the column to return the value from.

That’s it. Such a NOOB mistake. Thank you!

Please, if the answer solves the question click :heavy_check_mark:.

Error 502 occurs when you are passing incorrect/insufficient arguments to a function which it deserves. For your case, VLOOKUP expects entire table range and you should identify the column number properly from the selected table range.

Details of Err:502 is covered with examples:


=VLOOKUP($A1,Sheet1.$A$2:$A$27,3,0) Error (,) is (:wink:


ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.