excel formül arşivleri - PEAKUP TEKNOLOJİ A.Ş.

The VLOOKUP Function

Hello everybody,

As you can understand from the title, this article will be pretty long.
If you want to become an expert in the VLOOKUP formula, you can read this article till the end and apply it.

It is a function that everybody has heard of, everybody wants to learn and that everybody uses in Excel. We use the VLOOKUP function to find a value in the table.
Let’s explain it a bit: we use the VLOOKUP function when we search a data in a table or data range and want to get data from a column that corresponds to that data. For example: let’s assume that you have a personnel salary table. In one cell there is the personnel name, you can search your employee’s name and find out the salary they get easily.

SYNTAX

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

To express how the VLOOKUP function works simply:

=VLOOKUP (look for this; look in this table; if you find it bring me the one in the 2nd column; [TRUE – Approximate Match / FALSE – Exact Match])

Now let’s take a look at the arguments this function wants from us.

Argument name	Description
lookup_value (required)	The value you want to look up. The value you want to look up must be in the first column of the range of cells you specify in the *table_array* argument. For example, if *table-array* spans cells B2:D7, then your lookup_value must be in column B. *Lookup_value* can be a value or a reference to a cell.
table_array (required)	The range of cells in which the VLOOKUP will search for the *lookup_value* and the return value. You can use a named range or a table, and you can use names in the argument instead of cell references. The first column in the cell range must contain the *lookup_value*. The cell range also needs to include the return value you want to find. Learn how to select ranges in a worksheet.
col_index_num (required)	The column number (starting with 1 for the left-most column of *table_array*) that contains the return value.
range_lookup (optional)	A logical value that specifies whether you want VLOOKUP to find an approximate or an exact match: Approximate match – 1/TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don’t specify one. For example, =VLOOKUP(90,A1:B100,2,TRUE). Exact match – 0/FALSE searches for the exact value in the first column. For example, =VLOOKUP(“Smith”,A1:B100,2,FALSE).

Working Conditions

When the required arguments are not entered, the function doesn’t work.
If the range_lookup optional argument is not entered, TRUE- Approximate match is considered as default.
The lookup_value always has to be on the far left (in first column) of the selected/indicated table array. But this situation can be overcome by writing different functions in VLOOKUP. (We will see this below.)
If the lookup_value is not found on the left of the table_array, #N/A error is returned.
If there is a multiple number of the data written into the lookup_value argument in the range indicated in the table_array argument, this function returns the record of the first data it found
it is not checked if the text written into the lookup_value argument is with uppercase or lowercase.
when a numeric data is written into the lookup_value argument, if the data type stated as table_array in the first column is text, #N/A error is returned. the same condition is valid in the opposite condition.
if a smaller number than 1 or a bigger number than the column number in the indicated table range or a text is written into the col_index_num argument, #VALUE! error is returned.
when a column is deleted from the field stated as table_array, #REF! error is returned.
1 and 0 can be written instead of TRUE and FALSE in the range_lookup argument.
If TRUE – Approximate Match is chosen, the table assumes that the first column will be sorted by number or alphabetically and looks for the most approximate value on the base. If you don’t specify a method, this method is used as default.
If FALSE- Exact Match is chose, if there is a value that is exactly the same with the data you have stated in the lookup-value argument is look for in the first column of the table_range.

THE BASIC USE OF THE VLOOKUP FUNCTION

You see a search sample suitable with the basic language and working conditions of the VLOOKUP function in the image below.

We have said that if you find the name Sami Önder in the B column which is the first column in the B3:D9 cell range , bring the data in the 3rd column of the selected range.

We have mentioned that we can write 0 instead of FALSE. With this FALSE expression, it i checked if there is an exact Sami Önder in the B column.

USING WILDCARD CHARACTERS

We can get the same result without having to write the full name we are looking for on the same table by using a wildcard character after writing the name only. Let’s brush up on the Wildcard Characters that we can use in Excel.

We have 3 different Wildcard characters.

Use	To find
? (question mark)	Any single character For example, sm?th finds “smith” and “smyth”
* (asterisk)	Any number of characters For example, *east finds “Northeast” and “Southeast”
~ (tilde) followed by ?, *, or ~	A question mark, asterisk, or tilde For example, fy06~? finds “fy06?”

The asterisk and question mark act as complementary character when it is in the beginning or end of a text in the lookup_value argument of the VLOOKUP function.
And ~ only limits the other wildcard characters by preceding them.

If the wildcard character precedes the text, it implies that the text ends with that. For example: *ali (if it ends with ali)
If the wildcard character is at the end of the text, it implies that the text starts with the. For example: ali* (If it starts with ali)
If the wildcard character is both in the beginning and at the end of the text, it implies that the texts contains that. For example: *ali* (if the text contains the word ali)
here is an example for the question mark: when you write ?ali, it means that there is a character (text, numbers or symbols) before the word ali.
You can analyze this image as an example of the situation I have mentioned. ‘=F4&”*”

METHODS OF GETTING DATA FROM MULTIPLE COLUMNS

All Functions in Excel -except for the recently added dynamic array functions- return only one result.
When we get a result with the VLOOKUP formula, we might want to get the other data in the table by dragging the formula right.
Instead of writing the formula over and over again for each column, we can get data from the others column in the field we have stated as table_array when we drag the cell we have written the formula in right after adding a function to the col_index_num function that would change it as dynamic.
We can execute this action with a few methods. Now, let’s analyze these methods.

Our first method..

Let’s assume that we have a table like the one here.
We will get the data in the I.D. Number and Salary fields when we write the Name of the Personnel and drag it right.

We will have to write our formula correctly to execute an action like that.
And by correctly I mean using the cell styles correctly.
Which mean will we pin the cells while selecting the cell or cell range or not, or are we going to pin the row or column only?
As an answer to these questions, we have to write the $ (dollar) symbols in the direction it is supposed to be when we select the cell.
And then we will have to use a function that would allow us the make the col_index_num part dynamic.

Well, let’s start.

While selecting a cell in the formula, we have to think about the pinning issue we have mentioned.
Since the place of the cell in which we write the Name of the Personnel is stable, we press F4 and pin it.
And since the place of the cell range we selected for the table_array argument, we press F4 again and pin that cell range as well.
And our formula will look like this as you can see in the image.
=DÜŞEYARA($G$2;$B$1:$E$7;2;0)
DÜŞEYARA is the Turkish word for VLOOKUP. So in English it would be:
=VLOOKUP($G$2;$B$1:$E$7;2;0)

And when we drag our formula right, since we wrote 2 manually to the col_index_num argument, that part will always stay as 2.
Our aim here is to change the col_index_num argument by increasing 2,3,4 when we drag the formula right and to get the data in those columns.
Right at this moment comes the COLUMN function to our help.

What is the COLUMN function? How is it used?

The COLUMN function returns the column number of the given cell reference.
It is used in two ways.

When it is written as =COLUMN(), gives the number of the column of the cell in which it is written.
When it is written as =COLUMN(reference address) gives the number of the column of the cell stated as the reference address.

If you write the =COLUMN() formula into the E10 cell, since the E column is the 5th column in Excel, the formula will return the number 5.
Likewise, if you write the =COLUMN(B1) formula into the E10 cell, since the B column is the 2nd column in Excel, the formula will return the number 2.
Since we didn’t pin anything in the B1 cell address, when we drag the formula right it will change as C1, D1, E1. And this allows us to get the numbers 2,3,4,5.
Thanks to this, we will be able to get the data from the other columns in the table_array range.

You can see the sample formula below for the situation we want.

Our second method…

We will not use an auxiliary function in this method, but since the argument that will change will be col_index_num, we will try to get a result with the array in that part.
Which means that we will write the column index numbers we want to get as {2;3;4} and make it an array formula and make each one of these numbers transmitted to each column.

Here is how we’ll do it:

Edit the formula in the H2 cell like in the image.
Choose the field we will transmit the result of the formula to. (H2:J2)
Press F2 and get into the cell.
Press Ctrl + Shift + Enter and complete the formula entry.

Being able to write the col_index_num we want into the col_index_num argument within {…..} curly braces will allow us to get the data in the columns we want and grant us with flexibility.

VLOOKUP FROM THE RIGHT

As you know, look ups are done from left to right all the time in Excel. Look ups can be from left to right in columns are in characters of a text.
And many functions have been created and released with the same logic. The VLOOKUP function is one of these functions.

As we have mentioned in the Working Conditions above, the lookup_value always has to be in the first column of the table_array in the VLOOKUP function. If it is not found, the formula returns the #N/A error.
By the way, this is not an “error” like we think it is, it is just a warning meaning that the looked up value does not exist in the table.
Getting an error when the data we look up is not in the first column of the range we have stated is a code related result of the VLOOKUP function.

Let’s have an example of this situation if you please.
If the cell range chosen for the table_array argument starts from the A column while looking for the personnel named Murat OSMA, an error will be returned since there will not be a personnel name in the first column of the field we have selected. This is completely normal.

We can arrange this working style of the VLOOKUP function that causes us to get an error the way we want.
There are a few different methods for this too, but I will try to explain the easy one for you.

We have a function named CHOOSE.
This function requires an index_num and selecting arrays, i.e. data clusters.
The formula written in the image below is: =ELEMAN(1;C1:C7;B1:B7) [ELEMAN is the Turkish version of the CHOOSE function. Thus, in English it would be: =CHOOSE(1;C1:C7;B1:B7)]
We have written 1 as the index_num in the formula, and then chose each data range (arrays); first the C1:C7 range and then the B1:B7 range.
Since we wrote 1 into the index_num and choose the C1:C7 data range as the 1st data range, this formula will return the data in the C1:C7 range.
If we had written 2 into the index_num, it would have returned the data in the B1:B7 range.

We get the data in the column we want when we state these index_num and arrays.
You see the formula you can use to prevent errors and get the data you want.
We state in which range the result should be by writing it in col_index_num within { … } in the index_num argument
by using the CHOOSE function in the table_array argument in the VLOOKUP function.
This way, if you write 1 into the col_index_num, you will get the result in the B2:B7 range and if you write 2, you will get the result in the A2:A7 range.

USING THE RANGE_LOOK UP ARGUMENT [TRUE – APPROXIMATE MATCH] (1)

We usually use the VLOOKUP function to get the data that matches exactly with the value we are looking for.
And 95% of the people who use this argument use the [range_lookup] argument as FALSE – Exact Match, i.e. 0.
So, what is this TRUE – Approximate Match?

When you look at the argument, it explains as:“the data in the first column of the table_array should be listed in an ascending order.”
So, when the [range_lookup] value is chosen as TRUE, sort the first column of the table_array before using the VLOOKUP.
If you don’t sort it, it is pretty likely to get the wrong data unless in an exceptional situation.
If the data in the table_array field is not sorted, if even if there is one that exactly matches, the #N/A error is returned.

When should you use TRUE – Approximate Match?

If the data in the table_array field is sorted, we can use the TRUE – Approximate Match.
If we are looking for a Numeric data and there is a number that matches with it, or there is no number that matches with it we can use it to bring the closest number to it.

Now, let’s have an example of this situation.
What we want to do in the formula written in the image below:
We want to find the Name of the Personnel of the Salary that is in the base of the number 3300 or numbers close to it.
For this action to give the correct result, keep in mind that the data in the first column of the table_array argument should be sorted.
The formula we write will give use the result of the name of the Personnel with the Salary of 3250 -since there is no one with the Salary of 3300 and this is the closest one-, so the name of Sami Önder.

VLOOKUP BY TWO OR MORE CRITERIA

We have used the VLOOKUP function to find a data in the table and then get the data from the column we wanted so far.
If we want, we can also get a result depending on multiple criteria.

We have stated in the image below that we want to get this: Get the Name of The Personnel whose salary is 2500 and whose Place of Birth is İzmir.
And we used this formula for this:
=SUBSTITUTE(VLOOKUP(G2&H2&”*”;B1:B7&D1:D7&C1:C7;1;0);G2&H2;””)
We have used this technique here: We joined the Salary(Maaş in Turkish as you see in the image) and Place of Birth(Doğum Yeri in Turkish) in the lookup_value argument (G2&H2) and added &”*” at the end, and thus got to state that our conditions start with these.
And we have joined the ranges of Salary, Place of Birth and the Name of Personnel that we want to get as a result in the table_array argument.
Since we have joined the ranges we wanted to get and we were looking for with & in a single field, we have written 1 in the col_index_num argument.
We have written 0 for Exact Match in the range_lookup argument.
As a result, it returns us the 2500İzmirSami Önder text.
Lastly, when we say leave the data we write in the lookup_value argument blank in the text returned from the SUBSTITUTE function,
here will be the Personnel Name left Only.
This way, we got to find the data we were looking for by multiple criteria.

WHAT YOU SHOULD DO WHEN THE DATA TYPE YOU ARE LOOKING FOR AND THE DATA TYPE YOU FIND IS DIFFERENT?

Sometime even though a data should be numeric, it is determined as text because of the call style.
In this case, you might have realized that Excel warn you with green triangles and yellow warning symbol on the top left of these cells.
When you click that warning symbol, you will see the Number Stored as Text expression.
This explanation tells us that the data in the cell is a number but since the cell style is text, I store this number you have written as a text.

If the data type in the cell stated as lookup_value is a text,
the data type of the first column of the cell range we have stated as table_array must be text as well.
Or you can think of the opposite situation. To sum up: the types of the looked up and found data should be the same.
If they are not the same, the #N/A error is returned.
You can see the example about this in the image below. The formula used is:
=VLOOKUP(G2;B1:E7;2;0)
Since the data type of lookup_value is text, and the data in the first column of the table_array is numbers, there is no match and the #N/A error is returned.

So, how do we solve this? It is very simple!

We can solve this problem by applying a mathematical operation that would turn the text stated as the lookup_value into a number.
The mathematical operation to be applied shouldn’t affect the existing number.
For example, if we use any mathematical operation like +0, *1, /1, ^1 or — (plus 1, multiply by 1, divide by 1, exponent 1, hyphen hyphen), we will get the result you can see below.
The formula used: =VLOOKUP(G2+0;B1:E7;2;0)

USE VLOOKUP INSTEAD OF WRITING NESTED IFS

In some of our tables, for example when we want to calculate annual leave, we can write many nested IF function and find the solution that way.
But the VLOOKUP gives us the solution to solve this calculation faster and easier.
Now, let’s make the same calculation with nested IFs and then with VLOOKUP. You will see the difference.

Let’s assume that we have a table like the one below. We will determine different rations depending on the different processes. If we want to do it with nested If functions, we will have to write the formula long like this:

=IF(C2=”CV Search”;10%;IF(C2=”Bizde Mülakat Aşamasında”;20%;
IF(C2=”Aday Paylaşıldı”;40%;IF(C2=”Müşteride 1.Mülakatta”;50%;
IF(C2=”Müşteride 2. Mülakatta”;70%;IF(C2=”Müşteri Assessment Aşamasında”;80%))))))

We have shared this with the Turkish sentences so that you can see the cells in the table. However, the formula would be like this with the English translation of these sentences:
=IF(C2=”CV Search”;10%;IF(C2=”In the interview stage with us”;20%;
IF(C2=”The candidate has been shared”;40%;IF(C2=”In the 1st interview with the customer”;50%;
IF(C2=”In the 2nd interview with the customer”;70%;IF(C2=”The costumer is in the Assessment stage”;80%))))))