Instructions on How to Use the VLOOKUP Function in Excel

Excel software is one of the extremely useful calculation tools for accountants, especially if you have mastered skills such as functions that will make work smoother, such as VLOOKUP, HLOOKUP,  etc. INDEX,...  The most prominent of which is  the use of the VLOOKUP function in Excel.  WebTech360 will give you an overview as well as instructions for using the above functions.

Overview of the VLOOKUP . function

VLOOKUP is a basic function of Excel and brings a lot of benefits to users. All versions of Excel from old to new can use this function. Therefore, it is convenient for those who are working with different versions of Excel.

People use the VLOOKUP function in Excel to search for data. In other words, for a given table this function has the main function of finding certain values.

When to use the VLOOKUP function in Excel?

In Excel, there are many different functions for calculating and searching. With each function will usually have its own use. Sometimes a function can do more than one function. However, you need to clearly understand which functions should be used in order to work more conveniently.

Using VLOOKUP function in Excel is mainly done when you need to find anything in a given table or by range of rows. For example, you need to look up the types of products that are preferred by customers, the prices of those items, ...

Generally, there are two main ways to use the VLOOKUP function in Excel. Depending on the purpose of the user to choose one of two ways as follows:

- Exact search. This case applies when an exact match needs to be searched (Range_lookup = FALSE). The function will give an error if there is no value in the row that matches the value you are looking for.

- Relative search. This is the find method used when you need to search by range of values ​​with a relative match. To put it simply, the VLOOKUP function will return the maximum value and less than the lookup_value in the field where you can't find an exact match.

How to manipulate the VLOOKUP function in Excel is simple

To use the VLOOKUP function in Excel, follow the syntax as follows:

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

Accordingly, the above formula means:

VLOOKUP is known as the function name.

lookup_value is the value you need to look up aka the lookup value.

table_array here means lookup table.

col_index_num is the column position containing the return value.

range_lookup: for exact match you enter 0, for relative find value you enter 1.

Please perform the above operations and practice regularly so that you can master how to use the VLOOKUP function in Excel. You can ask for help from others if you are new and have difficulty using this function.

Example of using the VLOOKUP function in Excel

To help you better understand how to use the VLOOKUP function in Excel, we will give two specific examples. You can follow how to do it right below.

Use the VLOOKUP function to find the exact value

Example: Suppose you have a data table of a list of goods as shown below. In this table, there are names of items, objects, unit price, quantity, import tax, fee, unit cost, selling price, profit. In this case, you need to determine the import tax depending on the object of each item. So how do you have?

To find the import tax, enter the following formula in cell G5:

=VLOOKUP(D5,$D$17:$F$20.2,0)*E5

In there:

VLOOKUP is the function we use to find the import tax value in the tax regulation table.

D5 are the objects 1,2,3,4 to look for in the list of goods.

$D$17:$F$20 is the lookup table. Here the dollar sign is used for the purpose of permanently copying the formula. When dragging the result from cell G6 to cell G12, the formula will not be changed.
2 is the import tax column. This is the position of the value column to get.

0: search for exact match. Here, we need to find the absolute value so enter 0 or FALSE.

E5 is the unit price of the product to help us determine the import tax.

After performing the above formula, we will have the result as shown below. To display all results, copy the formula from cell G5 to cell G12 .

Use the VLOOKUP function to find relative values

Use the VLOOKUP function in Excel to have a relative search performed when the search values ​​have been sorted in ascending or descending order. If the values ​​are unsorted before execution, the VLOOKUP function will return the wrong result.

Example: You need to rank each student's academic performance based on the average score, you will apply relative search (Range_lookup=1).

How to do: You enter the following formula in cell D4 in column D (category column):

=VLOOKUP (C4,$B$10:$C$12,2,1)

or: =VLOOKUP (C4, $B$10:$C$12,2,TRUE)

Configure computer as Office - Word, Excel

In addition, you can refer to the following configuration to help you work in excel smoothly as well as perform functions without lag.

🔵 Configure OFFICE PC - INTEL CORE i3 10100 | RAM 8G 

➤ MAINBOARD: MSI H410M-A PRO
➤ CPU: INTEL CORE i3 10100 up to 4.3G | 4 CORE | 8 THREAD
➤ RAM: LEXAR 8GB DDR4 2666MHz (1x8GB)
➤ SSD: GIGABYTE SSD 240G SATA III
➤ HDD: CUSTOMER OPTIONS 
SOURCE: AEROCOOL VX PLUS 400 400W
➤ CASE: JETEK KAMADO ( NO FANS )

These are the methods that help you to  use the VLOOKUP function in Excel ,  WebTech360  believes that with the above basic instructions, you have understood how to operate excel more smoothly as well as bring high efficiency in learning and job.