Vlookup function in Excel

Vlookup is a column value search function that is one of the most used search functions. You've probably heard of the Vlookup function but don't know how to use the Vlookup function yet. The following article shows you how to use Vlookup function in Excel.

Vlookup function in Excel Picture 1

Description

The Vlookup function is a column lookup function, which searches for a value in the first column from the left of the data table. If the function is found, it will return one of the next columns in the same row as the value in the first column you specify.

The function is usually used to fill information into the main table with data taken from the secondary table.

The syntax of the Vlookup function

= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

Inside:

- lookup_value: value to search, this value can be a string, a reference or some value of the main table. If the lookup_value value is smaller than the smallest value in the first column of the table_array sub-table, the Vlookup function returns the error value.

- table_array: reference table or table containing the values ​​to search, the values ​​in the first column of table_array are the values ​​searched by lookup_value. This value can be text, number or logical value.

- col_index_num: index column in table_array will get the value returned to the main table. If col_index_num value is less than 1 or greater than the number of columns in table_array, the function will return an error value.

- range_lookup: is a logical value that helps you specify the Vlookup function to find exact or relative searches.

+ If range_lookup is TRUE or omitted, the function will relatively detect the values ​​in the first column in table_array must be arranged in order to avoid Vlookup function returning incorrect values.

+ If range_lookup is FALSE value , the function searches exactly the values ​​in the first column in table_array without any sort order.

For example:

- Detect relative.

The following data sheet is available, please fill in the HK Class and Symbol information based on the Class table information .

Vlookup function in Excel Picture 2

- Fill information for the HKType column , how to enter the formula as follows:

First, select the mouse in the SV Ranking box of SV01, enter the formula as follows: you enter = VLOOKUP (E6, then you use the mouse to select (black out) the Sort sub-table , then release the mouse button and press the next F4 key you enter, 2,1).

The formula you get is as follows: = VLOOKUP (E6, $ E $ 12: $ G $ 16,2,1).

Inside:

E6 is the value to be searched (above example is 80).

$ E $ 12: $ G $ 16 is the table of values ​​to be searched (the above example is the Ranking table).

2 is column 2 in the table of information values ​​to be searched, the data of column 2 will be filled in the main table (the above example is the HK Rank column in the Ranking table).

1 is relative detection.

Vlookup function in Excel Picture 3

- Enter the information for the Symbol column , you enter the same formula as the HK Rank column but the col_index_num value in VLookup you choose is 3, corresponding to the column will be taken the value in the Sort column as Symbol column .

= VLOOKUP (E6, $ E $ 12: $ G $ 16,3,1)

Vlookup function in Excel Picture 4

- Absolute detection (correct) you only need to change the range_lookup value to 0 or FALSE , but if the lookup_value value is not found in the first column of the secondary reference table, the function will report an error.

With this simple example, I hope you will understand how to use Vlookup function in Excel. Depending on the actual requirements, you can use the Vlookup function to process data on Excel spreadsheets. Good luck!

4 ★ | 2 Vote

May be interested

  • How to filter duplicate data on 2 Excel sheetsHow to filter duplicate data on 2 Excel sheets
    to filter duplicate data from 2 sheets in excel, you can use the vlookup function.
  • How to use Hlookup function on ExcelHow to use Hlookup function on Excel
    hlookup function basically also has the function syntax and features like vlookup function, which is to help users find data in excel table, with the conditions or given information. here is the guide for using the hlookup function in detail.
  • Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCHLook up data in Excel tables: Replace VLOOKUP with INDEX and MATCH
    quick lookup of data in excel tables: replace vlookup with index and match. do you know yet? let's tipsmake.com find out in the article below!
  • VLOOKUP function - Usage and detailed examplesVLOOKUP function - Usage and detailed examples
    vlookup is one of the most used excel functions. the following article details how to use and detailed examples when using the vlookup function.
  • How to combine Vlookup function with Left functionHow to combine Vlookup function with Left function
    when combining the vlookup function with the left function, we will easily separate the values ​​to find the right information we need.
  • How to use the IF function with VLOOKUP (examples and how to)How to use the IF function with VLOOKUP (examples and how to)
    how to use the if function in combination with vlookup (examples and how to do it). if you are looking to use the if function with vlookup with specific examples, please refer to the following article. the following article will guide you 3 common cases using the if function
  • LOOKUP function searches in ExcelLOOKUP function searches in Excel
    the lookup () function returns a value from a data range consisting of 1 column or 1 row, or from an array. the lookup () function is an improvement of the two functions vlookup () and hlookup () because it has the additional function to distinguish the search area as rows or columns.
  • How to use the INDEX function in excel?How to use the INDEX function in excel?
    along with vlookup and hlookup, an extremely interesting search function that you should not ignore is the index function. how to use it? is it difficult or not?
  • ISNA function in ExcelISNA function in Excel
    using excel documents shared from friends or on the internet, you sometimes come across isna () combined with other functions like vlookup () and if (). you wonder why they use isna () function so?
  • How to use the LOOKUP function in excel?How to use the LOOKUP function in excel?
    the lookup function is more advanced than the hlookup and vlookup functions. the lookup function can be used when you need to look at a row or a column and find a value