Vlookup, a quick way to find the value of a condition in an excel report
Vlookup is one of the functions that I use a lot in excel and I think it is good to write something about it because when I started to use it, I was a little confuse about how. This formula is very useful to look for a specific result based in 1 or more particular conditions. To resume, it can find and display what I want by just telling it to search for 1 common value.
To explain better, let’s use a simple example. In the cell F2, I put this formula:
=VLOOKUP(A3,A:D,3,0)
It is the same thing if I put that way “=VLOOKUP(101,A:D,3,0)” because the value of cell A3 is 101
Explanation:
- A3 is the cell of my condition so I am asking it to find “101”
- A:D is where to look for so it will search between the column A and D. In fact, it will search in the column A for “101”, once it will find it, it will display the result located between B and D.
- 3 is the third column where I want it to display the corresponding value once it finds “101”. Take note that 3 is not C but the third from A.
For instance, I want to know the number of the team3:
=VLOOKUP(C4,C:D,2,0)
As you see, D is 4 but I put 2 because it is the second column after C. More examples, I want the number of the ID 103, the formula will be:
=VLOOKUP(A5,A:D,4,0)
Or I want the date for the ID 100:
=VLOOKUP(A2,A:D,2,0)
For this last example, I don’t need to put all columns, putting this one will give me the same result:
=VLOOKUP(A2,A:B,2,0)
As you can guess, if you want to display the value of the column C, if you put only the first 2 columns, you will get an error, for instance “=VLOOKUP(A2,A:B,3,0)” so you have to include it, “=VLOOKUP(A2,A:C,3,0)”
Vlookup works only in “forward” not in “backward”, I mean, you can’t ask the formula to display the ID for the team2. For instance, if you put this formula, you will get an error:
=VLOOKUP(C2,A:D,1,0) or =VLOOKUP("team2",A:D,1,0)
The condition should be always on the first column, it can be on the column A or in the column C, it doesn’t matter as long as the result is located after, for instance:
=VLOOKUP(C3,C:D,2,0)
So this formula will find the team2 in the column C and once it will find it, it will display the corresponding value on the column D.
Taking back the example to display the ID for the team2, I have 2 options:
- 1. Move the column C to A and put the formula “=VLOOKUP("team2",A:D,2,0)”
- 2. Or to create a new column at the beginning and put this formula “=VLOOKUP(A2,A:E,2,0)”
NOTE: in the “ref group” column, I put in cell A2 this simple formula “=D2” and just copy and paste below. I don’t copy the values of the column D to column A because every time I will extract the data, the original values will change.
The most important thing to take in consideration is to put between brackets any words, for instance:
=VLOOKUP("team2",A:D,2,0) because if you put without for instance “=VLOOKUP(team2,A:D,2,0)”, you will get an error
In the other hand, don’t put between brackets number because you will get an error. As you can see, neither for a cell reference.
For a date, there are 3 options. I will use back the original example with 4 columns:
- 1. =VLOOKUP(B2,B:D,3,0) that you get now familiar with
- 2. =VLOOKUP(INT("01.06.2019"),B:D,3,0)
- 3. =VLOOKUP(G2,B:D,3,0) so I put the date into another cell and reference to it
For the second option, I put the INT function because putting the date only will not work, this is because excel, even if I don’t see it, it is taking in consideration the date and time. Putting the INT function, I am just telling excel to focus only on the date.
The other thing to take in consideration, vlookup don’t care about duplicate, it will look for the first one and nothing more. Imagine that in the “group”, I have only “team1” so if I put this formula in the cell F2:
=VLOOKUP("team1",C:D,2,0) or =VLOOKUP(C4,C:D,2,0)
It will only display me the corresponding value of the column D for the first one that it will find, meaning “8”. The only way to display the ones below, it is to put this formula for each cell in the column E by referencing the row number:
=VLOOKUP("team1",C2:D2,2,0) or =VLOOKUP(C2,C2:D2,2,0)
As for the other formulas, you can combine vlookup with others to get the result you want, for example, in one of the formulas above, I combine it with the formula INT.
Interesting Topics
-
Be successfully certified ITIL 4 Managing Professional
Study, study and study, I couldn’t be successfully certified without studying it, if you are interested...
-
Be successfully certified ITIL 4 Strategic Leader
With my ITIL 4 Managing Professional certification (ITIL MP) in the pocket, it was time to go for the...
-
Hide visual and change background color based on selection
Some small tricks to customize the background colour of a text box...
-
Stacked and clustered column chart or double stacked column chart
In excel, I use a lot the combination of clustered and stacked chart...