What Is VLOOKUP True In Google Sheets?
The VLOOKUP TRUE in Google Sheets function permits an approximate match or lookup within the table’s first column, which provides greater flexibility than exact matches.
Google Sheets VLOOKUP True feature can be useful when dealing with typos or small variations in data entries. Predominantly, everybody uses FALSE as the range criteria because we need an exact match from the range. In this article, we will learn to use the TRUE criteria with different scenarios.
- VLOOKUP in Google Sheets helps users retrieve data in a dataset, such as, name, ID, etc. and then pull corresponding data from the same row across different columns.
For instance, consider the table with items and their values. The cells D1:E2 are the result cells.
Select cell E2, enter the formula =VLOOKUP(E1,A2:B3,2,TRUE) and press the Enter key.
We can see the result as 10, as shown in the above image.
Table of contents
Key Takeaways
- VLOOKUP True in Google Sheets is used to simplify searching for specific data within large datasets or if we must find a specific value within a smaller dataset. Itis an efficient and powerful tool that saves time and reduces errors when manipulating datasets.
- The value of TRUE is represented by the numerical digit 1. While dealing with numerical scenarios, the system will consistently locate the value that is less than or equal to the search_key within the range, which ensures accurate and efficient data retrieval.
- The function allows users to extract information from one table based on a matching value in another table. Ensure the reference tables are properly sorted and the search_key matches exactly.
Syntax
The syntax of the VLOOKUP formula in Google Sheets is,
The arguments of the VLOOKUP formula in Google Sheets are,
- search_key: It is the value searched in the table’s first row.
- range: It refers to the cell range of the dataset from which the data is retrieved.
- index: It refers to the column number in the “range”. If “index” is set to 1, 2 and so on, the function returns the value from the first column, the second column and so on from the range.
- [is_sorted]: The “is_sorted” parameter determines whether the function should perform an approximate or exact match. A value of TRUE (1) indicates an approximate match, while a value of FALSE (0) indicates an exact match. If neither match is found, the function returns the #N/A error.
All the arguments of the VLOOKUP function are mandatory, except for the last argument, i.e., [is_sorted], which is optional.
How To VLOOKUP Closest Matching Result By Using TRUE Option?
Let us consider the following example to understand how we can find the VLOOKUP Closest Matching Result by Using TRUE Option.
You can download this VLOOKUP True Google Sheets Template here – VLOOKUP True Google Sheets Template.
Now, take a look at the below dataset. We have the numbers from 3 to 20. On the right-hand side, we have a search_key of 14, but this number does not exist in the main numbers table.
First, let us apply the VLOOKUP function with FALSE as the range criteria to find the exact match. Therefore, select cell D3 and enter the formula =VLOOKUP(C3,A2:A9,1,0).
Now, press the “Enter” key to get the result of the formula.
As a result, we have an error value of not available #N/A.
Now, change the range criteria from FALSE (0) to TRUE (1). Then, the formula will be =VLOOKUP(C3,A2:A9,1,1).
The output displayed is 10, as shown below.
However, we were trying to retrieve the number 14, which does not exist in the range. How did this parameter return 10 as a result?
It resulted so because, we have set the [is_sorted] argument as TRUE, so it finds the closest match for the search_key provided (14) and the VLOOKUP starts to search from top to bottom when the search_key is lesser than the value in the table it will stop, and then return the respective result.
In our data, 14 is greater than 10 and less than 15, so the moment VLOOKUP finds the value 15, it returned the previous smaller value, 10.
To test this, let us copy the values A1:A9 and paste it in different cells, F1:F9. Now, change the value in the dataset, cell value F5 from 10 to 15.
Now, enter the formula in cell D3 as=VLOOKUP(C3,A2:A9,1,1) [the new cell range, but the same data except for F5 cell value change.]
The output is shown above as 8. Since, we changed the current lesser value more than the lookup, it has returned the previous lesser value, 8.
Examples
We will consider some examples for VLOOKUP TRUE in Google Sheets.
Example #1
XYZ retail store intends to get the salary details of each of its employees. In the data table below,
- Columns A, and B shows names and salary respectively.
- Cells D1:E3 are the result cells.
The steps to fetch the whole table using the VLOOKUP are as follows:
- Step 1: Select cell E2 and enter the formula =VLOOKUP(D2,A2:B6,2,TRUE).
- Step 2: Next, press the Enter key. We can see the result in cell E2. Then, drag the formula from cell E2 to E3 using the fill handle to get the output as shown below.
Example #2
The salary details and deductions made by the company for each of its employees are reflected below.
- Columns A, B and C shows the names, annual salary and deduction.
To calculate “Deduction%”, we have the below criteria.
- If the annual salary is <100000 and <400000, then the deduction % will be 6%.
- If the annual salary is <400000 and <600000, then the deduction % will be 8%.
- If the annual salary is <600000, then the deduction % will be 10%.
The steps to calculate the deduction percentage using the VLOOKUP TRUE are as follows:
- Step 1: Select cell C2 and enter the formula is =VLOOKUP(B2,$A$9:$B$11,2,TRUE).
- Step 2: Next, press the Enter key. We can see the result in cell C2. Then, drag the formula from cell C2 to C6 using the fill handle to get the output as shown below.
VLOOKUP TRUE As Alternative To IF Condition
IF() function is an important function as we use IF statements for all the criteria-based calculations. However, we can use the VLOOKUP TRUE As Alternative To IF Condition.
For example, look at the below data.
We have two tables: the “Sales Table” and the “Incentive % Table”.
- For the “Sales Table,” we need to arrive at an “Incentive %” based on the revenue generated by each employee.
- To calculate “Incentive %,” we have the below criteria.
- If the revenue is >50000, then the incentive % will be 10%.
- If the revenue is >40000, then incentive % will be 8%.
- If the revenue is >20000, then incentive % will be 6%.
- If the revenue is <20000, then incentive % will be 5%.
So, we have four criteria to satisfy. In these cases, we use typical IF conditions to arrive at incentive %, but we will use the VLOOKUP formula with TRUE as the criteria to reach incentive %.
The steps to calculate the incentive % using the VLOOKUP TRUE As Alternative To IF Condition are as follows:
Step 1: Select cell C3 and enter the formula =VLOOKUP(B3,$E$3:$F$6,2,1)
Step 2: Next, press the Enter key. We can see the result in cell C3. Then, drag the formula from cell C3 to C9 using the fill handle to get the output as shown below.
We have our incentive % against the revenue generated by each employee, as shown above.
Output Observation: First, take a look at the Incentive % table.
- It says between 0 to 20000, incentive % is 5%.
- Between 20001 to 40000, incentive % is 6%.
- Between 40001 to 50000, incentive % is 8%.
- Anything above 50000, incentive % is 10%.
Since we have provided TRUE as the range argument, it will return the approximate match.
Therefore, in cell B3, the revenue is 35,961, less than the incentive table value of 40,000, and less than 40,000 in the table is 20,000. So, for this, the “Incentive %” is 6%.
Important Things To Note
- VLOOKUP TRUE finds the approximate match and it is also represented by 1.
- In the case of a numerical scenario, it always finds the value less than or equal to the search_key in the range. If the search_key is lesser than all the values in the range, then, it will return a #N/A error. To avoid this error, ensuring that the search_key falls within the range of values present in the dataset.
Frequently Asked Questions (FAQs)
VLOOKUP True and VLOOKUP False are Google Sheets functions used to find and retrieve information from a table by performing a vertical lookup. The key difference between the two is that,
• VLOOKUP True uses an approximate match while VLOOKUP False uses an exact match.
• VLOOKUP True will return the closest match value that is less than or equal to the search_key. On the other hand, VLOOKUP False will only return values that exactly match the search_key. While this may limit its use in some situations, it ensures complete accuracy and can be vital when working with text-based data such as names or codes.
VLOOKUP True is a powerful tool for,
• Data analysis and decision-making.
• Performing dynamic information filtering based on specific criteria rather than manually sorting data tables.
• When dealing with large datasets that require complex comparison and lookup operations.
For instance, in sales data where products have different prices depending on the region or customer type, using VLOOKUP True can enable us to quickly find the appropriate price for a particular transaction without manually sorting through the entire dataset.
The accuracy of data results may be affected while using the VLOOKUP function in Google Sheets with the True option. The True option, also known as approximate match finds the closest match to a given value within a specified range. While this can be useful in some cases where exact matches are not possible or necessary, it can lead to incorrect data if used improperly.
The VLOOKUP TRUE function in Google Sheets has the following limitations:
It only works when the lookup column is arranged in ascending order. This means errors can occur if the dataset is unordered or duplicated.
VLOOKUP TRUE does not provide exact matching of values and can return incorrect results when matching similar but not identical text strings or alphanumeric characters. Additionally, using VLOOKUP TRUE on large datasets can slow down the performance of the workbook.
VLOOKUP TRUE only returns one value per search_key.
Download Template
This article must help understand VLOOKUP True In Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to VLOOKUP True In Google Sheets. Here we use the VLOOKUP formula to find an approximate match instead of an exact match with examples.. You can learn more from the following articles.
Leave a Reply