Google Sheets VLOOKUP Function

VLOOKUP Function

The VLOOKUP function is a premade function in Google Sheets, which allows searches across columns.

It is typed =VLOOKUP and has the following parts:

serach_key: Select the cell where search values will be entered.

range: The table range, including all cells in the table.

index: The data which is being looked up. The input is the number of the column, counted from the left:

[is_sorted]: TRUE/1 if the range is sorted or FALSE/0 if it is not sorted.

Let's have a look at an example!

Vlookup Function Example

Lookup and return Pokemon names from this list by their ID#:

# Name Type 1 Type 2 Total 1 Bulbasaur Grass Poison 318 2 Ivysaur Grass Poison 405 Search ID# 3 Venusaur Grass Poison 525 Name 4 Charmander Fire 309 5 Charmeleon Fire 405 6 Charizard Fire Flying 534 7 Squirtle Water 314 8 Wartortle Water 405 9 Blastoise Water 530 10 Caterpie Bug 195 11 Metapod Bug 205 12 Butterfree Bug Flying 395 13 Weedle Bug Poison 195 14 Kakuna Bug Poison 205 15 Beedrill Bug Poison 395 16 Pidgey Normal Flying 251 17 Pidgeotto Normal Flying 349 18 Pidgeot Normal Flying 479 19 Rattata Normal 253 20 Raticate Normal 413

Copy Values

The VLOOKUP function, step by step:

1. Select the cell H4
2. Type =VLOOKUP
3. Click the VLOOKUP command

H4 is where the search result is displayed. In this case, the Pokemon's names based on their ID#.

4. Select the cell where search value will be entered (H3)

H3 selected as serach_key. This is the cell where the search query is entered. In this case the Pokemon's ID#.

5. Type ,
6. Specify the table range A2:E21

7. Type ,
8. Type the number of the Name column, counted from the left: 2

9. Type TRUE
10. Hit enter

In this example the table is sorted by ID#, so the [is_sorted] value is TRUE.

An illustration for selecting column index number 2:

Now, the function returns the Name value of the search_key specified in cell H3:

Good job! The function returns the #N/A value. This is because there have not been entered any value to the Search ID# cell H3.

Let us feed a value to it, type 7 into cell H3:

Have a look at that! The VLOOKUP function has successfully found the Pokemon Squirtle which has the ID# 7.

One more time, type 4 into cell H3:

It still works! The function returned Charmanders name, which has 4 as its ID#. That's great.

Let's try another example, using the Pokemon names as input instead.

First, change the places of columns A and B.

Clicking and holding coloumn A and dragging it between columns B and C will rearrange them like this:

Now, the function is trying to look up 4 in the Name column, which returns the #N/A error.

Let's switch the labels, and try to enter Caterpie into the cell H3, where the vlookup functions finds the search_key:

Notice that the ID# returned is 1, although Caterpie's ID# is actually 10.

This result is another error.

This is because the Name values are not sorted like the ID numbers are.

Let's change the value of the [is_sorted] part of the function from TRUE to FALSE:

Now, the function correctly returns Caterpie's real ID number: