Thursday, 30 January 2020
In today’s post we are going to talk about an Excel function that I personally find quite useful. The function in question is “V-Lookup”.
The first thing is to see what Excel itself says about its function:
“Looks for a value in the first left column of a table, and then returns a value in the same row from a specified column. By default, the table is sorted in ascending order”
A priori it may sound somewhat confusing, but basically what Vlookup does is take a value that we specify, look for it in a certain matrix (defined by us) and return the data of the column that we want from the same row where it found the value we were looking for. In other words, it will look for a value that we have in an Excel sheet, in another Excel sheet and will return the information we need associated with that value.
Let’s see the parameters that make up the function:
- Valor_buscado: It is the value that we want the function to find us in the other list.
- Matriz_buscar_en: The set of data (list, table, sheet…) where we want to locate our value.
- Indicador_columnas: Here we will have to indicate, with numerical value, the column from which we want to return the data associated with the value sought. It is important to keep in mind that the column number that we must enter is relative since column one will always be the first of the matrix to be searched.
- Ordered: It tells us if we are looking for an approximate or exact value. It is best to always put false (look for exact value) to avoid obtaining biased data.
Knowing this we are ready to see an example. One of the two main utilities I find in this function is to compare two listings when both have different data about the same “entity” and we need to merge them into a single list.
For example we have a list where we have a customer id, the client and its phone number. On the other hand we have a list with the consumption of telephone lines. If we want to know when each client has consumed without having to search and compare we can make use of the Vlookup function.
The two lists that we will use in the example belong to different sheets of the same Excel workbook, but the function can be used even between different workbooks.
Sheet1:
Sheet2:
Well what we are going to do is for each client to get the consumption of their line, using for it the telephone line itself that is the common value in both listings. In column D of our sheet one we will establish the following formula:
=VLOOKUP(C2, Sheet2! A1:B10;2; FALSE)
The first thing we have established is the value we want to look for, that is, column C2. Secondly we have chosen the matrix where we want to look for that value, columns A and B up to row 10 on sheet2. The next thing is to tell you what value we want to return once you locate “C2”, in this case taking as column 1 the first column that makes up the matrix where we are going to search, we want you to look for column 2, that is, the “Consumption in €”. Finally we put FALSE so that the match is exact.
The result:
As you can see he returns 70 which is the value that this line has associated in sheet2.
In conclusion, a simple way to unite data from different sources in one place, as long as they have at least one value in common.In future installments we will see another of the great utilities of this function: Check values between listings
