Use the Excel Vlookup function, syntax and examples

Use the Excel Vlookup function, syntax and examples


The Vlookup function is a data lookup function in Excel, using the Vlookup function when you need to find everything in a table or a range by row. The following article Taimienphi.vn will guide you how to use Vlookup function in Excel most specifically.


NOTE: Download Now 70+ basic and advanced excel exercises

VLOOKUP function In Excel, the function to search values ​​by column and return to the vertical method, this can be said that Excel function is quite popular and convenient for people who use Excel to statistic and search data by exact columns. . In the framework of tips learn excelTaimienphi.vn will present to readers two basic ways of detecting this function.

Vlookup function examples, Vlookup function exercises in Excel

Table of Contents:
I. Vlookup function syntax
II. Vlookup function example in Excel.
III. The Vlookup function combines the Hlookup, Left, Right, Match functions.
IV. Correct the #NA Vlookup function error.

How to use the Vlookup function in Excel 2016, 2013, 2010 and 2007?

I. Vlookup function syntax

Note the function syntax:
VLOOKUP (lookup_value, table_array, col_index_num,[range_lookup])

Inside:

lookup_value: The value to use for detection.
table_array: Detector value table, set to Absolute address (marked with $ in front by pressing F4).
col_index_num: The order of the columns to retrieve data on the probe value table.
range_lookup: Search range, TRUE is equivalent to 1 (relative detection), FALSE is equivalent to 0 (absolute detection).

II. Vlookup function example in Excel

1. How to find relative:

Example: Based on the table of grading rules corresponding to the given score, conduct academic grading for students named in the list below:

Ham vlookup in excel

Excel Vlookup function example

We use the formula in column D6 as: = VLOOKUP (C6, $ C $ 16: $ D $ 20,2,1)

Results:

Ham vlookup in excel

Results using the vlookup function

2. Absolute detection.

With absolute detection, you will find more detail than relative detection.

Example: Fill in the information Home town and level of employees into the table based on the corresponding employee code below.

  Great for using vlookup

Absolute tracking example when using vlookup function

To enter employee’s hometown information, use the Vlookup formula for cell E6 as follows: = VLOOKUP (A6, $ D $ 12: $ F $ 17,2,0)

A6 is the value to be searched for

$ D $ 12: $ F $ 17 is a lookup table

2 : number of the column of data on the lookup table

0 : Accurate detection type

Using Excel vlookup in Excel

How to use absolute detection with Vlookup function?

Similarly to fill in the qualifications box, we do the following:

With cell F6 has the formula: = VLOOKUP (A6, $ D $ 12: $ F $ 17,3,0)

How to use vlookup in Excel

The result uses the Vlookup function to detect absolute values

Relative and absolute values

With absolute values, you will find the most accurate results. Absolute values ​​will not change addresses even though they appear in new columns and cells. Therefore we use absolute values ​​in the case of finding the most accurate information and using the absolute values ​​in the case of finding the most accurate and near-accurate results. To learn more, readers can refer to the article about Reference functions in Excel Please.

III. The Vlookup function combines the Hlookup, Left, Right, Match functions

In the content on Taimienphi.vn guided you to learn the basics of the Vlookup function, to help you understand and apply it more practically, now we will give a specific exercise situation as follows. This problem is a combination of Vlookup and Excel functions such as Hlookup, Left, Right and Match.

Give data as shown in the following table with the following convention:
– Table 1: RICE STATISTICS TABLE
– Table 2: TABLE OF GOODS, MANUFACTURER AND PRICE TABLE

Ham vlookup hlookup hlookup left right match

Request to resolve:

Question 1: Based on the 2 characters to the left and the 2 characters to the right of the Product Code in Table 1, look in Table 2 to fill in the values ​​for the Row Name – Manufacturer Name column (Calculate the data for column C5: C10).
For example: KD-ND That is, Khang Dan – Nam Dinh rice.
Question 2: Fill in the Unit Price for each item based on the Product Code in Table 1 and look in Table 2. (Calculate the data for column D5: D10).
Question 3: Calculated into money = Quantity * Unit price

Answer:

Question 1:
– Explain: We need to come up with the formula to get the data of Name of Goods and Name of Manufacturing Province, then combine these two calculation formulas to get the answer Question 1.

+ Get Product Name: Take the 2 characters to the left in the Product Code column in Table 1 (A5: A10) compared to the 2 characters in the Item Code column in Table 2, but the data area we get to the entire Row Name column in Table 2 (A15: B20) or (A15: E20).
>> We use Vlookup function: In a cell that doesn’t have data, enter the following formula to try: = VLOOKUP (LEFT (A5,2), $ A $ 15: $ B $ 20,2, FALSE)
After finishing typing, press Enter, if the word “Khang Dan”, that means you have completed 40% of the answer to this Question 1 already, simple right, continue.

+ Get the Province of Production: Taking the 2 characters to the right in the Product Code column (A5: A10) in Table 1 compared to the 2 characters in the “Row” Code of Rice – Name of province of production in Table 2, the data area will be obtained (A16: E20).
>> Get the data compared in rows so we use the Hlookup function. Also in a cell without data, try entering the following formula:

= HLOOKUP (RIGHT (A5,2), $ C $ 16: $ E $ 20,2, FALSE)
After finishing typing, you also continue to press Enter, if the word “Nam Dinh”, that is, you have completed an additional 40% of Answers 1. Now you have figured out the answer, right? The next task is to combine these two functions to calculate the data in the Name Row – Name of Manufacturing column

+ Matching 2 formulas: There are many formulas to concatenate or concatenate strings together, in this problem, Taimienphi.vn guides you how to concatenate strings using the & function. In this problem, we will use hyphens (with spaces) to separate the two formulas that have calculated the previous results, that is, the Name of Goods and the Province of Manufacturing, namely: “- “
In general, the function will look like this = + vlookup & “-” & hlookup (No more = = at the top of the Hlookup function)
At that time, at C5, you enter the formula:
C5 = + VLOOKUP (LEFT (A5,2), $ A $ 15: $ B $ 20,2, FALSE) & “-” & HLOOKUP (RIGHT (A5,2), $ C $ 16: $ E $ 20,2, FALSE)
Press Enter to see the result, If the standard answer is “Khang Dan – Nam Dinh”
The result is ok, in the next cell is C6: C10, you just need to point the mouse at the edge of the formula in cell C5 and scroll down to C10, that’s it.
Similarly,

C6 = + VLOOKUP (LEFT (A6,2), $ A $ 15: $ B $ 20,2, FALSE) & “-” & HLOOKUP (RIGHT (A6,2), $ C $ 16: $ E $ 20,2, FALSE)
That’s it, Question 1 of the request has been given, continue to solve Question 2 of you

Question 2: Calculating Unit Price
The formula will look like this: In cell D5, enter the formula
D5 = + VLOOKUP (LEFT (A5,2), $ A $ 16: $ E $ 20, MATCH (RIGHT (A5,2), $ A $ 16: $ E $ 16,0), FALSE)

Question 3: Calculated into money
Oh, this question often appears in math problems such as payrolls, expenses, sums of money, etc.It’s pretty easy, isn’t it?
The formula for calculating the sum will be as follows, In cell F5 you enter E5 = + D5 * E5

IV. Correct the #NA Vlookup function error

What is #NA error?

The #NA error is returned in an Excel formula when an appropriate value cannot be found

When using the Vlookup function, we get the #NA error when we can’t find a search condition in the viewport, exactly here is the first column in the conditional area of ​​the Vlookup function

How to fix a #NA error in the Vlookup function

Reference: Ways Correct the #NA error in the Vlookup function

In Excel, the commonly used Vlookup function makes it possible to perform a value search function quickly and smoothly. Currently, you can apply one of two ways to detect the Vlookup function.

With the two relative and absolute ways of searching Vlookup, the function of looking up values ​​by columns you can easily statistic data on Excel spreadsheets. Vlookup helps to make detailed statistics to make the report filter out the necessary lists, your work will be more accurate and shorter. This function is used quite a lot during spreadsheet operations.

To learn well and understand the search function, you can refer to the Excel Hlookup function introduced by Taimienphi.vn. The Hllookup function helps you to search and reference by column, or by row, when you use this function, if you meet the conditions set.

Besides the Vlookup search function in Excel, you can refer to the COUNTIF function in Excel, the COUNTIF function is a pretty convenient counting function, with the initial conditions, the COUNTIF function will give you the most accurate results.

The calculation function also has quite a few different functions in Excel, and the SUMIF function is one of the functions that many people use, SUMIF function is a conditional sum function that helps you to sum columns and individual rows without summing all the data in an Excel file.

https://thuthuat.taimienphi.vn/huong-dan-su-dung-ham-vlookup-in-excel-633n.aspx
Using Excel cannot help but mention the MID Function, this is Basic functions in Excel Support cutting strings in a given sequence of characters in Excel spreadsheets, the basic MID function is used a lot in cases where the most special characters in long strings can be extracted. to hundreds of characters

.

Related Posts

Leave a Reply

Your email address will not be published.