All the secrets of the Excel VLOOKUP function for finding data in a table and extracting it into another

When working in Excel, very often there is a need to find data in one table and extract it into another. If you still do not know how to do this, then after reading the article, you will not only learn how to do this, but also find out under what conditions you can squeeze the maximum performance out of the system. Most of the very effective techniques that should be used in conjunction with the VLOOKUP function are considered.

Even if you have been using the VLOOKUP function for years, then with a high degree of probability this article will be useful to you and will not leave you indifferent. For example, being an IT specialist, and then a manager in IT, I have been using VLOOKUP for 15 years, but I only managed to deal with all the nuances now, when I began teaching Excel to people on a professional basis.

VLOOKUP is an abbreviation for vvertical NSinspection. Likewise, VLOOKUP - Vertical LOOKUP. The very name of the function hints to us that it searches in the rows of a table (vertically - iterating over rows and fixing a column), and not in columns (horizontally - iterating over columns and fixing a row). It should be noted that VLOOKUP has a sister - an ugly duckling who will never become a swan - this is the HLOOKUP function. HLOOKUP, as opposed to VLOOKUP, performs horizontal searches, but the concept of Excel (and indeed the concept of data organization) implies that your tables have a small number of columns and a much larger number of rows. That is why we need to search by rows many times more often than by columns. If you use the HLO function too often in Excel, then it is likely that you did not understand something in this life.

Syntax

The VLOOKUP function has four parameters:

= VLOOKUP (;; [;]), here:

- the desired value (rarely) or a reference to a cell containing the desired value (the vast majority of cases);

- reference to a range of cells (two-dimensional array), in the FIRST (!) column of which the parameter value will be searched;

- column number in the range from which the value will be returned;

- this is a very important parameter that answers the question whether the first column of the range is sorted in ascending order. If the array is sorted, we specify the value TRUE or 1, otherwise - FALSE or 0. If this parameter is omitted, it defaults to 1.

I bet that many of those who know the VLOOKUP function as flaky, after reading the description of the fourth parameter, they may feel uncomfortable, since they are used to seeing it in a slightly different form: usually they are talking about an exact match when searching (FALSE or 0) or a range scan (TRUE or 1).

Now you need to strain and read the next paragraph several times until you feel the meaning of what was said to the end. Every word is important there. Examples will help you figure it out.

How exactly does the VLOOKUP formula work?

Formula type I. If the last parameter is omitted or specified equal to 1, then the VLOOKUP assumes that the first column is sorted in ascending order, so the search stops on the line that immediately precedes the line containing the value that is greater than the desired value … If no such string is found, then the last row of the range is returned.

Image
Formula II. If the last parameter is specified as 0, then the VLOOKUP scans the first column of the array sequentially and immediately stops the search when the first exact match with the parameter is found, otherwise the # N / A (# N / A) error code is returned.

Param4-False

Formulas workflows

VPR type I

VPR type II

Corollaries for formulas of the form I

Formulas can be used to distribute values across ranges.
If the first column contains duplicate values and is sorted correctly, then the last of the rows with duplicate values will be returned.
If you search for a value that is obviously greater than the first column can contain, then you can easily find the last row of the table, which can be quite valuable.
This view will return the # N / A error only if it does not find a value less than or equal to the desired one.
It is rather difficult to understand that the formula returns the wrong values if your array is not sorted.

Corollaries for formulas of type II

If the desired value occurs multiple times in the first column of the array, then the formula will select the first row for subsequent data retrieval.

VLOOKUP performance

You have reached the climax of the article. It would seem, well, what's the difference if I specify zero or one as the last parameter? Basically, everyone indicates, of course, zero, since this is quite practical: you do not need to worry about sorting the first column of the array, you can immediately see whether the value was found or not. But if you have several thousand VLOOKUP formulas on your sheet, you will notice that VLOOKUP II is slow. At the same time, usually everyone begins to think:

I need a more powerful computer;
I need a faster formula, for example, many people know about INDEX + MATCH, which is supposedly faster by a measly 5-10%.

And few people think that as soon as you start using VLOOKUP of the type I and ensure that the first column is sorted by any means, the speed of VLOOKUP will increase 57 times. I write in words - FIFTY-SEVEN TIMES! Not 57%, but 5,700%. I checked this fact quite reliably.

The secret of such fast work lies in the fact that an extremely efficient search algorithm can be applied on a sorted array, which is called binary search (halving method, dichotomy method). So VLOOKUP of type I applies it, and VLOOKUP of type II searches without any optimization at all. The same is true for the MATCH function, which includes a similar parameter, and the LOOKUP function, which only works on sorted arrays and is included in Excel for compatibility with Lotus 1-2-3.

Disadvantages of the formula

The disadvantages of VLOOKUP are obvious: firstly, it searches only in the first column of the specified array, and secondly, only to the right of this column. And as you understand, it may well happen that the column containing the necessary information will be to the left of the column in which we will be looking. The already mentioned combination of INDEX + MATCH formulas is devoid of this drawback, which makes it the most flexible solution for extracting data from tables in comparison with VLOOKUP (VLOOKUP).

Some aspects of applying the formula in real life

Range search

A classic illustration of a range search is the task of determining a discount by order size.

Search for text strings

Of course, VLOOKUP looks not only for numbers, but also for text. It should be borne in mind that the formula does not distinguish between the case of characters. If you use wildcards, you can organize a fuzzy search. There are two wildcards: "?" - replaces any one character in a text string, "*" - replaces any number of any characters.

Fighting spaces

The question is often raised how to solve the problem of extra spaces when searching. If the lookup table can still be cleared of them, then the first parameter of the VLOOKUP formula is not always up to you. Therefore, if the risk of clogging the cells with unnecessary spaces is present, then you can use the TRIM function to clear it.

Different data format

If the first parameter of the VLOOKUP function refers to a cell that contains a number, but which is stored in the cell as text, and the first column of the array contains numbers in the correct format, then the search will fail. The opposite situation is also possible. The problem can be easily solved by translating parameter 1 into the required format:

= VLOOKUP (−− D7; Products! $ A $ 2: $ C $ 5; 3; 0) - if D7 contains text and the table contains numbers;

= VLOOKUP (D7 & ""); Products! $ A $ 2: $ C $ 5; 3; 0) - and vice versa.

By the way, you can translate text into a number in several ways at once, choose:

Double negation -D7.
Multiplication by one D7 * 1.
Zero addition D7 + 0.
Raising to the first power D7 ^ 1.

Converting a number to text is done through concatenation with an empty string, which forces Excel to convert the data type.

How to suppress # N / A

This is very convenient to do with the IFERROR function.

For example: = IFERROR (VLOOKUP (D7; Products! $ A $ 2: $ C $ 5; 3; 0); "").

If VLOOKUP returns the error code # N / A, then IFERROR will intercept it and substitute parameter 2 (in this case, an empty string), and if no error occurs, then this function will pretend that it does not exist at all, but there is only VLOOKUP that returned normal result.

Array

Often they forget to make the reference of the array absolute, and when stretching the array "floats". Remember to use $ A $ 2: $ C $ 5 instead of A2: C5.

It is a good idea to place the reference array on a separate sheet of the workbook. It does not get underfoot, and it will be safer.

An even better idea would be to declare this array as a named range.

Many users, when specifying an array, use a construction like A: C, specifying entire columns. This approach has a right to exist, since you are saved from having to keep track of the fact that your array includes all the required strings. If you add rows to the sheet with the original array, then the range specified as A: C does not need to be adjusted. Of course, this syntactic construct forces Excel to do a little more work than specifying the range exactly, but this overhead can be neglected. We are talking about hundredths of a second.

Well, on the verge of genius - to arrange the array in the form.

Using the COLUMN function to specify the column to extract

If the table into which you are retrieving data using VLOOKUP has the same structure as the lookup table, but simply contains fewer rows, then you can use the COLUMN () function in VLOOKUP to automatically calculate the numbers of the columns to be retrieved. In this case, all VLOOKUP formulas will be the same (adjusted for the first parameter, which changes automatically)! Note that the first parameter has an absolute column coordinate.

Creating a composite key with & "|" &

If it becomes necessary to search by several columns at the same time, then it is necessary to make a composite key for the search. If the return value were not textual (as is the case with the "Code" field), but numeric, then the more convenient SUMIFS formula would be suitable for this and the composite column key would not be required at all.

This is my first article for a Lifehacker. If you liked it, I invite you to visit, and also gladly read in the comments about your secrets of using the VLOOKUP function and the like. Thanks.:)

How to fit all your vacation essentials into one bag and carry it in your carry-on baggage

Competent packing of things for a trip will allow you not to drag a huge suitcase with you and not to overpay for your luggage. Take advantage of these effective tips

Vulnerability of most modern processors opens access to all passwords and personal data

Almost all processors are subject to a serious vulnerability. Using it, attackers can gain access to all logins and passwords, cached files and any other personal data of users. The conditionally vulnerability allows for two types of attacks, which are named Meltdown and Specter