Contents
Necessary foreword
If you have not worked with the function before VPR (VLOOKUP), then we lost a lot, I highly recommend that you first read this article and watch the video tutorial in it.
Problem
As many people know, the function VPR (VLOOKUP) can produce as a result values that are strictly to the right of the column where the search is performed. Oh, how beautiful it would be if the third argument of this function (the number of the column where the values are taken from) could be set to negative, but no.
In practice, situations often arise when you need to look for data in a column that is to the right, and not to the left of the results column, for example:
It is easy to find the cost by the order code – the usual VLOOKUP will help here once or twice. But how to find the name of the product by code? At trainings, I most often hear this question in the wording “how to make a left VLOOKUP”?
Let’s look at a few ways.
Method 1. Frontal attack
If you follow Occam’s principle and do not complicate unnecessarily, then you can simply copy the desired column to the right (or make it links) and use the usual VPR:
Cheap and cheerful, but requires manual finishing of the table. In addition, there are often cases when the table cannot be changed: it is password protected, it is a corporate template, the table is shared, etc. Then a different approach is needed.
Method 2. Virtual permutation of columns with the SELECT function
If it is impossible to rearrange the columns on the sheet, then this can be done virtually, i.e. “on the fly” right in the formula itself. For this we need a function SELECTION (CHOOSE). Its main purpose is to select the desired element from the list by a given number. For example, it can be used to replace the number of the day of the week with its text counterpart:
Nothing supernatural, at first glance, but there are a couple of tricky moments.
At first, instead of the textual names of the selected list items (“Mon”, “Tue”, etc.), you can use range addresses. And then the function will return a link to the selected range. So, for example, the formula:
=SELECT(2; A1: A10; D1: D10; B1: B10)
… will output a link to the second specified range (D1:D10).
Secondly, instead of a simple single number of the element to be retrieved in the first function argument SELECTION you can specify an array of constants in curly braces, like so:
=SELECT({1;2}; A1: A10; D1: D10; B1: B10)
Then at the output we will get the first two ranges (A1:A10 and D1:D10), glued into a single whole.
And now all this can be put inside our VPRto implement “left search”:
It differs from the “classic VLOOKUP” only, apparently, in that the range is set by gluing two columns Order code и Product using the function SELECTION. Everything else is normal.
The disadvantages of this method are the speed (about 5-7 times slower than the usual VLOOKUP) and some unusualness for colleagues (or maybe even a plus!)
Method 3. Linking the INDEX and MATCH functions
If you do not hold on to the VLOOKUP function, then you can use its more powerful counterpart – a bunch of two very useful functions INDEX (INDEX) и MORE EXPOSED (MATCH):
Function MORE EXPOSED searches for a given value (C2, i.e. the code of the order we need) in a one-dimensional range (column of codes in table C10:C25) and gives as a result the ordinal number of the cell where it found the desired one – in our case it will be the number 4, i.e. to. the code of the order we need is the fourth in the table.
And then the function comes into play INDEX, which can extract data from a vertical column array (product names in B10:B25) by serial number (which was previously found MORE EXPOSED). In this way, INDEX will give us the contents of the fourth cell from the column Product, which is what was required.
Compared to the previous method, this option is much faster to recalculate (almost as fast as a regular VLOOKUP), which is important for large tables.
I analyzed a similar example (with video) earlier in this article. And about the function INDEX you can talk for a long time 🙂
Method 4. Function SUMIF(MN)
If you need to extract exactly the number from the table (for example, the volume in liters), then sometimes it is easier to use the selective summation function to implement the “left VLOOKUP” SUMMESLI (SUMIF) or her older sister, the function SUMMESLIMN (SUMIFS):
The disadvantages of this approach are obvious – it only works for numbers and, provided that there are no duplicate values in the column. If there are duplicates (several orders with the same code), then this function will add up all the volumes, and will not return the first one, as VLOOKUP would do. Well, the speed of this method is also not very good – about 3-4 times slower than the usual VLOOKUP.
A large article about the functions of selective counting for one or more conditions is here.
Method 5. Ready macro function from PLEX
If you are not afraid of using macros, then you can use a ready-made user-defined function VLOOKUPS in Visual Basic, which is included with the latest version of my PLEX add-in for Microsoft Excel. Compared to conventional VLOOKUP, it can:
- search multiple columns at once (up to 3)
- return results from any column (to the left or to the right – it doesn’t matter)
- return not only the first encountered value, but the required one in order
- you can specify what to output if nothing is found instead of #N/A error
This method has two disadvantages: you need to save a file with macro support (XLSM) and the speed of any macro function is not very high – on large tables it can noticeably slow down.
But as one of the options – it will do 🙂
- What is the VLOOKUP function and how to use it to look up and lookup values
- How to display all values at once, and not just the first (Multi VLOOKUP)
- How to implement 2D search (VLOOKUP XNUMXD)