When using spreadsheet tools such as Excel and Google Spreadsheets in business, one thing to remember is the “VLOOKUP function.” Useful in all business situations.
This article provides an easy-to-understand explanation of the values used in the VLOOKUP function and how to create a VLOOKUP function using them. We also introduce examples of how to use the VLOOKUP function in
marketing
, so please take a look.
What is the VLOOKUP function?
The VLOOKUP function is an Excel function that searches existing data vertically and retrieves the values associated with the searched data. The V in the VLOOKUP function stands for “Vertical” and LOOKUP means “search”.
Visually searching for the necessary data from a huge amount of data such as Excel or Google Spreadsheets takes a tremendous amount of time and effort, but by using the VLOOKUP function, you can quickly extract it. This makes it possible to improve efficiency. The VLOOKUP function is one of the essential functions in business.
Differences with the new XLOOKUP function
The XLOOKUP function was launched in 2020 as the successor to the VLOOKUP function. The VLOOKUP function can only search vertically, but the XLOOKUP function also has the HLOOKUP function (horizontal) that searches horizontally.
Also, while the VLOOKUP function requires the search value to be placed on the left side, the XLOOKUP function does not require this and allows you to freely specify the search range.
VLOOKUP function syntax and four values used
The syntax of the VLOOKUP function is as follows.
=VLOOKUP(search value, search range, column number, search method)
When using the VLOOKUP function in this way, you need to set the following four values called arguments.
First, let’s check what each value represents.
search value
The “search value” listed first in the syntax of the VLOOKUP function specifies what data to look for, and is the key to extracting the necessary data. In the search value, enter the cell where you want to enter the data you want to search for, or the character string of the data you want to search for.
For example, if you want to enter the data you want to search in cell A3 located in column A, row 3 of the table, enter “A3” as the search value. Also, if you want to search for a value based on the information “1001”, you can simply specify the string “1001” as the search value.
In that case, surround the string with “” ” (double quotation marks) as “”1001”. Please note that full-width and half-width characters are distinguished in the search value, but uppercase and lowercase alphabetic characters are recognized as the same value.
range
The second “range” in the VLOOKUP function syntax specifies where to search for the search value. Select the table that contains the search value and set the search range.
At this time, it is important that the search value is in the leftmost column of the table specified in the range. The VLOOKUP function cannot extract data using the second or third column of a table as a search value. If you want to use the second or third column of the table as a search value and retrieve the values associated with it, you need to adjust the table so that the data you want to use as the search value is at the left end.
Also, if you devise a table that is specified as a range, it is possible to set multiple conditions for the search value. For example, if you want to specify both column B “Product number” and column C “Product name” as search values, add a column to the left end of the table and write “=B〇+C〇” in the cells of the additional column. Enter. Enter the number of rows (number) in the cell. By doing this, you can create a VLOOKUP function with two conditions as one search value.
column number
The third column number in the VLOOKUP function syntax specifies which column in the table you want to retrieve the value from. When specifying the column number, enter the number of the table column counting from the left. For example, if the data you want to extract is in the second column from the left of the table, specify the column number as “2”.
Search method
The fourth item in the syntax of the VLOOKUP function, “Search method,” specifies what to do if the search value is not found. Please enter TRUE or FALSE to specify the search method. If you specify TRUE, when the search value is not found, the maximum value that does not exceed the search value is displayed as the search result.
For example, if you search for “1000” and no match is found, the search results for the highest value not exceeding “1000” will be displayed instead. On the other hand, if you specify FALSE, an error (#N/A) will be displayed if the search value is not found.
This “search method” can also be omitted. Keep in mind that if omitted, it will be treated the same as TRUE. However, since the VLOOKUP function is often used to search for exact matching values, it is common to specify the search method as FALSE. Please specify TRUE and FALSE according to the purpose of using the VLOOKUP function.
How to create a VLOOKUP function
There are two ways to create a VLOOKUP function. One way is to use the function button in the formula bar. The function button is written as “fx” in Excel and “Σ” in Google Sheets.
First, select the cell where you want to use the VLOOKUP function and click the function button. After finding and selecting the VLOOKUP function through a search, enter each argument in order on the screen that appears to complete the VLOOKUP function.
Another method is to enter the formula “=VLOOKUP(search value, search range, column number, search method)” directly into the formula bar.
Here, we will explain step by step how to create the formula.
1.VLOOKUP input
First, select the cell where you want to input the VLOOKUP function. What we select here is the cell that will display the value extracted by the VLOOKUP function. After selecting the cell, enter “=VLOOKUP” in the formula bar.
=VLOOKUP
2. Specifying search values
Next, let’s specify the search value. Enter “(” (half-width parentheses) after “=VLOOKUP” and enter the cell where you want to enter the data you want to search, or the character string of the data you want to search. After entering “(“, enter the data you want to search. You can also click on the cell you want to input.
=VLOOKUP(A3 or =VLOOKUP(“1001”
3. Specify the range
Next, enter “,” (half-width comma) to specify the range. If the range of the table containing the search value is cells B5 to D20, please enter “B5:D20”. You can also select the range of cells by dragging after entering “,”.
=VLOOKUP(A3,B5:D20 or =VLOOKUP(“1001”,B5:D20
4. Specify column number
After specifying the range, enter “,” again, and then specify the column number. As mentioned above, enter the column number from the left in the table specified as the range in which the information you want to extract by search is located in the column number. If you want to retrieve the information in the second column from the left, enter “2”.
=VLOOKUP(A3,B5:D20,2 or =VLOOKUP(“1001”,B5:D20,2
5. Specify search method
Enter “,” again, then enter the search method at the end. As mentioned above, enter either TRUE or FALSE for the search method, but if you want to search only for values that exactly match the search value, enter FALSE.
=VLOOKUP(A3,B5:D20,2,FALSE or =VLOOKUP(“1001”,B5:D20,2,FALSE
6. Enter “)”
When you have finished entering the search method, enter “)” (half-width parentheses) at the end and press the “Enter key.” The input for the VLOOKUP function is now complete.
=VLOOKUP(A3,B5:D20,2,FALSE) or =VLOOKUP(“1001”,B5:D20,2,FALSE)
Example of using VLOOKUP function
You can use the VLOOKUP function to do the following:
Extracting specific data from large amounts of data
By using the VLOOKUP function, you can extract specific data from a large amount of data. For example, if you want to know the price of “Product 8” in the price list below, we will explain how to display it instantly at any location.
1. Click “Insert Function” from “Formula” in the menu
2. Set the function classification to “Show all” and select “VLOOKUP”
3. Select any cell you want to display the information (E2 in the above example)
4. The search value is “Product 8”, so specify “A9”
5. Select “Range” by dragging the range of the price list. (A1:B11)
6. Specify the data you want to retrieve by column number (here, it is the price from the price list, so enter “2” in the second column)
7. Enter “FALSE” in the search method to match the data exactly.
8. Click OK after entering everything
9. Then, the price of “Product 8” that you wanted to know will be displayed in the corresponding area.
Check the data
By using the VLOOKUP function, you can check for missing data by comparing two pieces of data and determining which data is only available in one. For example, if you want to compare the following two tables, Table 1 and Table 2, first add a check column to each table.
In the top cell of the check column in Table 1, enter “=VLOOKUP(001,Table 2,1,FALSE)”. At this time, by setting the range to “Table 2” and the column number to “1”, you can check whether the data in Table 1 exists in Table 2.
Similarly, in the check column cell of Table 2, set the range to “Table 1” and the column number to “1”, and enter the formula for the VLOOKUP function. Once the VLOOKUP function has been created for all cells in the check column, it will look like this:
:max_bytes(150000):strip_icc()/vlookup-cell-reference-360349cec2a644778049cab59e981f8d.png)
If there is data in only one of the tables, the search result will be “#N/A” (error), indicating that data is missing. In other words, “003,” “004,” and “007” do not exist in data A in Table 1, and “002,” “006,” and “008” do not exist in data B in Table 2.
In this way, by using the VLOOKUP function, you can easily check for omissions by comparing master data with other data.
Streamline data entry
For example, entering information about business partners in a sales list each time takes time and effort, and is prone to human errors such as input errors. Therefore, the company information of the business partner that is used repeatedly will be replaced with an ID, and the company information will be displayed from that ID information.
Next time, you can find the desired business partner information just by searching for the ID using the VLOOKUP function. For example, we will explain the procedure when you want to automatically enter the names of the persons in charge of company information IDs 005 and 006.
1. Prepare a sales list and define a name (here we will call it “company information ID”)
2. Place the reference company information ID in the leftmost column of the table.
3.Enter company information in advance into the list you have prepared.
4. Automatically input the name of the person in charge of the company information ID “IDno005” and “IDno006” from the list below into the desired cell.
5. Select any cell you want to display the information
6.In that state, click “Insert Function” from “Formula” in the menu
7. Set the function classification to “Show all” and select “VLOOKUP”
8. Enter “A6:A7” in the search value
9. Since the scope is the entire list, enter the “company information ID” set in the name definition.
10.The column number is the name of the person in charge, so enter “3” in the third column.
11.For search method, enter “FALSE” for exact match
12.Once everything is specified, click OK
13.Then it will be reflected as below
join tables
You can also join tables using the VLOOKUP function. For example, suppose you want to reflect the information in the “Product ID List” in the table of product sales in the table below.
1.Set the product name
2.Click “Insert Function” from “Formula” in the menu
3. Set the function classification to “Show all” and select “VLOOKUP”
4. Select any cell you want to display the information (D4 above)
5. Since the search value is “Product ID”, specify no1 to no10 “C4:C13”
6. Select “Range” by dragging the range in the product ID list. (H4:J13)
7 Specify the data you want to retrieve using the column number (here it is “Product Name” in the product ID list, so enter “2” in the second column)
8Enter “FALSE” for the search method to ensure an exact match of the data.
9. Click OK after entering
10.Then, the data will be reflected in the product name column of the number of product sales as shown below.
11.Next, reflect the unit price in the same way
12. Enter the following from the argument input screen of the VLOOKUP function.
13. Since the search value is “Product ID”, specify no1 to no10 “C4:C13”
14. Select “Range” by dragging the range in the product ID list. (H4:J13)
15. Specify the data you want to retrieve by column number (here it is “unit price” in the product ID list, so enter “3” in the third column)
16.Enter “FALSE” for the search method to match the data exactly.
17. Click OK after entering everything
18.Then, the unit price will be reflected as shown below.
Now you can join the two tables.
How to use the VLOOKUP function in marketing activities
Below are three examples of how to use the VLOOKUP function in marketing.
Use the VLOOKUP function to streamline your marketing operations.
Streamline vast amounts of manual input
By using the VLOOKUP function, you no longer have to manually enter huge amounts of data one by one. For example, let’s say you want to aggregate multiple tables into one, so you have to re-enter all the data into a new table.
If there is only a small amount of data, it is not too much effort to enter it manually, but if you enter a lot of data one by one, it becomes a tremendous amount of work. Also, if you do the work visually, you may be more likely to make mistakes due to misreading the data.
Therefore, if you use the VLOOKUP function to automatically extract the data that corresponds to the search value, you can prevent mistakes from occurring and join tables efficiently without spending much time.
Easily manage keywords for SEO
By using the VLOOKUP function, you can easily manage
SEO
keywords. Depending on the media or site, you may need to manage a large number of keywords for SEO purposes. For example, if you want to extract keywords that have been countered, it would take a huge amount of time to visually check each keyword and extract them.
For example, if you are managing URLs of treated content along with keywords in Excel or Google Spreadsheets, let’s say you set the URL as the search value for the VLOOKUP function. Then, you can easily extract the keywords for which countermeasures have been taken and the date on which the countermeasures were completed, allowing you to efficiently compile countermeasure information.
Extract only products with high repeat rate
The VLOOKUP function can also be used to extract popular products with a high repeat rate from product data. Understanding which products have a high repeat rate is essential for future sales expansion and stability. However, visually searching for products with a high repeat rate from a huge amount of data requires a lot of effort.
Here too, you can use the VLOOKUP function to perform ABC analysis of the product. ABC analysis is a method of analyzing all products by ranking them according to ABC. If you rank products based on high or low repeat rates, such as “A for repeat rate 〇% to 〇%,” you will inevitably be able to identify popular products with high repeat rates.
By performing ABC analysis using the VLOOKUP function, you can understand at a glance which products have a high repeat rate, leading to effective marketing.
Causes of errors and their solutions when using the VLOOKUP function
The VLOOKUP function is somewhat complex to operate and may generate errors. The main causes of errors are as follows.
When using the VLOOKUP function, “#N/A” may be displayed. “#N/A” is the most common error and is often caused by specifying a search value or range incorrectly. This is important to understand because the VLOOKUP function uses the leftmost column of the range as the search key.
Also, even if the search values are correct, you may not get the expected results. In this case, you need to make sure that the search value does not contain half-width symbols or that the data is sorted correctly. Excel distinguishes between half-width and full-width characters, so you should avoid using half-width characters in search values.
Additionally, the default search mode for the VLOOKUP function is approximate matching (1/TRUE), which may result in incorrect results for unsorted data.
The points to avoid errors with the VLOOKUP function are as follows.
By keeping these points in mind when using the VLOOKUP function, you can reduce the risk of errors. If you get an error, try checking the problem based on the points above.
Other useful functions
There are other useful functions besides the VLOOKUP function. I will introduce some of them.
XLOOKUP function
The XLOOKUP function was launched in 2020, and while the VLOOKUP function can only search data vertically, the XLOOKUP function can specify data search both vertically and horizontally. Also, as mentioned in this article, the VLOOKUP function does not work unless the search value is placed in the leftmost column, but the XLOOKUP function does not require that.
Since this method specifies the search range and return range, there is no need to specify column numbers. Due to this significant improvement in usability and increased versatility, the demand for the XLOOKUP function is expected to increase even more than the VLOOKUP function in the future.
HLOOKUP function
While the VLOOKUP function allows vertical data searches, the HLOOKUP function allows horizontal data searches. The VLOOKUP function places the search value in the leftmost column, while the HLOOKUP function places it in the top row. Other functions and settings are almost the same as the VLOOKUP function.
SUMIF function
The SUMIF function is used when you specify a condition and want to sum the numbers within that condition. For example, use it like this:
The syntax is expressed in the form “=SUMIF(range, search condition, [total range])”, and due to the nature of the function, it will not work unless you specify the “range” and “search condition”.
COUNTIF function
The COUNTIF function returns the number of cells that match the search criteria. This function makes it easy to aggregate data. For example, it is useful when aggregating the following data.
The syntax is “=COUNTIF(range, search condition)” and is very simple. It is highly versatile, simple, and easy to handle, so it can be used in a variety of situations and has many applications.
summary
The VLOOKUP function is a function that searches data vertically and extracts a specific value. In business, it is essential to use the VLOOKUP function to streamline operations. By referring to the creation method and usage examples explained in this article, you can make good use of the VLOOKUP function and use it for your marketing activities.