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.