Lookup
Looking up a city by code
Definition
Looking up a city by code means giving VLOOKUP the place code and a two-column table so it returns the matching city.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Arguments
| Argument | Meaning |
|---|---|
lookup_value |
The place code to find, such as "R-1". |
table_array |
The table to search. The code must sit in the leftmost column. |
col_index_num |
Which column of the table to return. 2 is the city column in this lesson. |
range_lookup |
Use FALSE for an exact match. |
Worked example
A depot keeps route codes and cities in A2:B4.
| A: Code | B: City |
|---|---|
| R-1 | Bath |
| R-2 | York |
| R-3 | Hull |
Formula: =VLOOKUP("R-1",A2:B4,2,FALSE)
Result: Bath. R-1 is in the first row of the table, and column 2 of that row holds Bath.
Practice
Codes N-1, N-2, and N-3 sit in A2:A4, and their cities Oslo, Rome, and Lima sit in B2:B4. In C2, write a formula that returns the city for N-3.
- In C2, write a VLOOKUP formula that returns the city for N-3 from A2:B4.
- C2 should show the city for N-3.
Step 1 of 2
Enter =VLOOKUP("N-3",A2:B4,2,FALSE) in C2.
N-3 is the last row, and the second column is Lima.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
C2 uses VLOOKUP.
C2 now shows Lima.
The next lesson is Picking a cell by row number.
Related functions
VLOOKUP is the function that returns that city from the code.
Common mistake
Asking for column 1, as in =VLOOKUP("R-1",A2:B4,1,FALSE): column 1 is the code itself, so the result is R-1 instead of Bath.