Google Sheets Function: VLOOKUP
The VLOOKUP function searches for a value in the first column of a cell range and then returns the value of a cell that is on the same row.
Usage:
=VLOOKUP(search_key, range, index, is_sorted)
Example of use
The objective here is to be able to enter the file number in cell E2 and then automatically obtain the desired information in the blue cells.
To start, the formula in cell G2 should return the name of the city:
Enter into the VLOOKUP function:
- search_key: the value to search for in the first column of the range (here, the file number)
- range: the range of cells containing all the data
- index: the column number of the range that contains the result to return (here, column 2 for the city)
- is_sorted: 0 to search for the exact value of search_key or 1 to return the closest value (provided the column is sorted), 0 is recommended in most cases
The formula here is:
=VLOOKUP(E2,A2:C11,2,0)
To then display the number of points, simply copy the formula and change the column number (replace 2 with 3):
If needed, you can copy the Google Sheets document (or view the document) with this example.
In this case, if the file number is not found, an error is returned. To return a value instead of this error, use the IFERROR function.
Since the VLOOKUP function searches for the value in the first column of the range, the results cannot be located to the left of the search column. To perform a search in a column that is not necessarily the first of the range, use the XLOOKUP function.