VLOOKUP
Excel VLOOKUP Function
Excel has a built-in function called VLOOKUP that enables cross-column searches.
It has the following components and is typed =VLOOKUP:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Note: The data used for the lookup must always be in the column on the left.
Note: A symbol, such as a comma or semicolon, is used to separate the various components of the function;
The symbol is determined by your language preferences.
Lookup_value: Choose the cell to be used as the entry point for search values.
Table_array: The range of the table that contains every cell in the table.
Col_index_num: The information that is being searched for. The column number, counted from the left, is the input.
Range_lookup: FALSE if the text(0) and TRUE if the numbers (1).
Note: Range_lookup allows for the use of both 1 / 0 and True / False.
How to use the VLOOKUP function.
- 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#:
The result of the search is shown in H4. The Pokemon names in this instance are determined by their ID#.
H3 selected as lookup_value. This is the cell where the search query is entered. In this case the Pokemons ID#.
Table range is indicated at table_array; for example, A2:E21.
In the 2 column from the left, which is the data that is being looked up, the number two is inserted as col_index_number.
An example of how to choose col_index_number 2.
Alright, range_lookup is entered as next – 1 (True). This is because there are only numbers in the column on the left. 0 (False) would have been used if it had been text.
Well done! The #N/A value is returned by the function. This is a result of the Search ID# H3 having no value entered.
Let’s provide it with a value of type H3(7):
Look at that, please! The Pokemon Squirtle, ID# 7, has been successfully located via the VLOOKUP function.
Once more, input (H3)4:
It is still functional! Charmander’s name, which has 4 as its ID#, was returned by the function. That’s fantastic.
