Buzz
VLOOKUP has 2 search modes: exact match and approximate match. By default, VLOOKUP is set to perform an approximate match. However, you can request VLOOKUP to search for an exact match or an approximate match. Previously, Mytour published an article 23 Insights into the VLOOKUP Function Everyone Should Know (Part 1) to help you grasp the basic knowledge of this function, and now the sequel 23 Insights into the VLOOKUP Function Everyone Should Know (Part 2) will further supplement your understanding of the VLOOKUP function.
23 Insights into the VLOOKUP Function Everyone Should Know (Part 2)
8. You can instruct VLOOKUP to search for approximate values
To have VLOOKUP search for approximate values, either omit the fourth argument (range_lookup) or input TRUE or 1. Below are 3 equivalent formulas:
=VLOOKUP(value, data, column)
=VLOOKUP(value, data, column, 1)
=VLOOKUP(value, data, column, TRUE)
Advice for you is though VLOOKUP doesn't demand, it's advisable to set the range_lookup argument explicitly. This way, you always have a visual reminder for the type of search you expect.
9. To use approximate match, data must be sorted
If intending to use approximate match, data must be sorted in ascending order by the lookup value. Otherwise, the results you obtain might be inaccurate. Additionally, textual data must be sorted, but not necessarily alphabetically.
10. VLOOKUP can merge data from different spreadsheets
VLOOKUP is also used to merge data from 2 or more spreadsheets. For instance, if you have one spreadsheet containing order data and another containing customer data, and you want to merge customer data into the summary order sheet for analysis.
Since the ID exists in both sheets, you can use this value to pull data into the desired data sheet using VLOOKUP. Simply set up VLOOKUP to use the ID value in sheet 1, and the data in sheet 2 with the required index.
In the example below, 2 VLOOKUP formulas are used. One to pull customer names and one to pull order statuses into sheet 1.
11. VLOOKUP can categorize data, categories
One of the most important things everyone should know about the VLOOKUP function is that if you need to apply arbitrary categories to data records, you can easily do this with VLOOKUP by using a spreadsheet as a 'key' to assign categories.
A classic example is letter grading, where you would have to map letter grades based on numerical grades:
In this case, VLOOKUP is configured to calculate the approximate value, hence the spreadsheet data must be sorted in ascending order.
Additionally, you can use VLOOKUP to categorize arbitrary groups. In the example below, VLOOKUP is used to categorize departments, using a spreadsheet as a 'key' to define the groups.
12. With absolute referencing VLOOKUP can be moved
In cases where information needs to be fetched from multiple columns in a table, or if VLOOKUP copy-pasting is required, you can save time by using absolute references for the lookup value and the lookup table. This allows you to copy the formula, and just change the column index to look up similar and fetch values from another column.
For example, because the lookup value and lookup table are absolute, you can copy the formula across columns, then go back and change the column indexes as necessary.
13. Named ranges make VLOOKUP easier to read (and easier to move)
The range of absolute cells looks quite messy and this makes the VLOOKUP formula appear rather hard to read, so to make the VLOOKUP formula visually appealing and easier to read, you should replace absolute references with Named ranges.
In the example of employee data above, you can name the input cell B3 as “id_code”, then name the data table as “data”, your formula will look like this:
Not only easier to read, but this formula is also easier to move because Named ranges are absolute.
14. Inserting a column can break existing VLOOKUP formulas
If spreadsheets contain VLOOKUP formulas, these formulas can be broken if you insert an additional column into the table. This is because the column index cannot automatically adjust when columns are inserted or deleted.
In this example, lookups for rankings and sales were broken when inserting a new column between the Year and Rank columns. The Year column still works because it's to the left of the inserted column:
To avoid this situation, you can calculate the referenced column index mentioned in the article 23 things about VLOOKUP everyone should know part two by Mytour.
In the article 23 Insights into the VLOOKUP Function Everyone Should Know (Part 2), Mytour just discussed and introduced to you some limitations as well as how to use the VLOOKUP function. To strengthen your knowledge of this function, you can refer to some sample VLOOKUP function exercises. Hopefully, the article provides you with additional useful information about the VLOOKUP function.
