1. Home

Excel VLOOKUP Function

The VLOOKUP function is a premade function in Excel, which allows searches across columns

VLOOKUP Function

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

It is typed =VLOOKUP and has the following parts:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Note: The column which holds the data used to lookup must always be to the left.

Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;

The symbol depends on your Language Settings.

Lookup_value: Select the cell where search values will be entered.
Table_array: The table range, including all cells in the table.
Col_index_num: The data which is being looked up. The input is the number of the column, counted from the left:
Range_lookup: TRUE if numbers (1) or FALSE if text (0).

Note: Both 1 / 0 and True / False can be used in Range_lookup.

How to use the VLOOKUP function.

Excel Skills for Business Specialization
  • Select a cell (H4)
  • Type =VLOOKUP
  • Double click the VLOOKUP command
  • Select the cell where search value will be entered (H3)
  • Type (,)
  • Mark table range (A2:E21)
  • Type (,)
  • Type the number of the column, counted from the left (2)
  • Type True (1) or False (0) (1)
  • Hit enter
  • Enter a value in the cell selected for the Lookup_value H3(7)

    • Let's have a look at an example!

    Use the VLOOKUP function to find the Pokemon names based on their ID#:

    H4 is where the search result is displayed. In this case, the Pokemons names based on their ID#.
    H3 selected as lookup_value. This is the cell where the search query is entered. In this case the Pokemons ID#.

    The range of the table is marked at table_array, in this example A2:E21.

    The number 2 is entered as col_index_number. This is the second column from the left and is the data that is being looked up.

    An illustration for selecting col_index_number 2.

    Ok, so next - 1 (True) is entered as range_lookup. This is because the most left column has numbers only. If it was text, 0 (False) would have been used.

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

    Let us feed a value to it, type H3(7):

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

    One more time, type (H3)4:

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

    Categories

    HTML CSS3 Bootstrap Foundation Sass JavaScript TypeScript JQuery AngularJS React NodeJS Highcharts PHP Python Python3 Django Linux Docker Ruby Java C C ++ Perl Servlet JSP Lua Scala Go ASP C # SQL MySQL SQLite MongoDB Redis Android Swift ionic Kotlin XML tutorial

    Contact US