Google Sheets Function: MATCH
The MATCH function searches for the position of a value in a range of cells.
Usage:
=MATCH(search_key, range, search_type)
Example of use
The MATCH function here needs to return the position of the searched city in the list of cities:
Enter into the MATCH function:
- search_key: the value whose position you wish to know
- range: the range of cells within which the function will search for the position
- search_type: 0 to find the exact value, 1 for the highest value less than or equal to search_key when the range is sorted in ascending order, -1 for the lowest value greater than or equal to search_key when the range is sorted in descending order (when in doubt, enter 0)
The formula here is:
=MATCH(E2,B2:B11,0)
In this example, "Dublin" is indeed the seventh cell in the range B2 to B11.
With the position returned by MATCH, it will then be possible to display the file number of the searched city as well as the number of points using the INDEX + MATCH combination.