Vlookup function shows me error "expect a number"

The error is exactly what the error message says.

Here is the description and syntax for VLOOKUP, as found in the Function Browser:

ƒx VLOOKUP

The VLOOKUP function returns a value from a collection of columns by

using the left column of values to pick a row

and a column number to pick a column in that row.

VLOOKUP(search-for, columns-range, return-column, close-match)

search-for: The value to find. search-value can contain any value.

columns-range: A collection of cells. columns-range must contain a reference to a single range of cells, which may contain any values.

return-column: A number value that specifies the relative column number of the cell from which to return the value. The leftmost column in the collection is column 1.

close-match: An optional modal value that determines whether an exact match is required.

close match (TRUE, 1, or omitted): If there’s no exact match, select the row with the largest left-column value that is less than or equal to the search value. If you use close match, you can’t use wildcards in search-for.

exact match (FALSE or 0): If there’s no exact match, return an error. If you use exact match, you can use wildcards in search-for. (To find information about wildcards, open the Help menu in your app and enter “wildcard” in the search field.)

Notes

VLOOKUP compares a search value to the values in the leftmost column of a specified collection. Unless an exact match is required, the row containing the largest left-column value that is less than or equal to the search value is selected. Then, the value from the specified column in that row is returned by the function. If an exact match is required and none of the leftmost-column values match the search value, the function returns an error.

Regards,

Barry

You may find the more modern function XLOOKUP more intuitive and flexible to use.

See explanation and examples here. It's not nearly as complicated as the numerous examples might make it seem.

SG

Continuing with what Barry was saying, an example might help. The data in my table is gibberish but the VLOOKUP function is looking in the leftmost column of the range A:C for the number 20 and returning the corresponding value from the 2nd column of that range. The zero in the function means "exact match only".