A quick notes on Vlookup Excel formula.
Vlookup formula is quite useful to find and locate data on Excel.
Table below will be used on how to demonstrate and use vlookup. But this basic idea is the same way you can apply even on a complicated or huge amount of excel data.
First we examine the parameters of Vlookup.
By typing Vlookup formula on Excel, Excel will prompt or display the parameters needed to complete the function.
Let's try to simplify or use lay man terms for the vlookup parameters.
In Excel 2007 that I'm using it displays these parameters below when I type Vlookup formula :
=Vlookup (look_up_value, table_array,col_index_num, [range_lookup])
The look_up_value is indeed the value that you're looking for or the data you're trying to find.
The table_array is the range of cells, or basically the whole cells or columns in which the data will be search.
The col_index_num, is the data that will be returned once the look_up_value matches any data define on the table_array.
The [range_lookup], let's just put a FALSE value on it to find the Exact Match.
Now that we know the parameters for the vlookup function, will use it to find some data on our example table below.
Let's say we know the asset number and we want to check who's the owner of it.
So in vlookup function, since we have the asset number it will be a good start to find some other data.
Vlookup function formula will be constructed like this:
=Vlookup(A11,A2:C20,3,False) will return Jude
=Vlookup(A12,A2:C20,3,False) will return Joachim
Let's examine the formula, the "A11" is the look up value for Asset Number ABC-3120, the "A2:C20" is the range of cells and column for the search area, the number "3" means return the value if the exact match is found, three columns from the look up value. First column is Asset Number, second column is Description, third column is Owner. Then the last parameter is "False" which tells vlookup to find for the exact match.
Now let's check the second vlookup formula, the value of "A12" is the look up value, A2:C20 is the search range area, number "3" is to return the value from the third column "Owner" if the exact match is found.
If the column index number in the vlookup formula was set to "2", then the second column labeled as "Description" will be the returned value for the vlookup.
You can try changing the vlookup formula to =Vlookup(A12, A2:C20,2,False), you will notice that returned value will be "Desktop", it is because you define on the formula, if there is an exact match get the value from second column.
You can try to experiment, changing the column index number and the look up value, but don't change the search range (don't change the search range if you copy the same table from this example) and don't change also the False parameter.
Vlookup is very useful to find and search data, but it will generate errors such as "#NA" and “#REF", I know you've seen those errors a lot and your eyes will turn big and rolls when such error occurs.
To avoid such error, we need to know how vlookup search for data. Vlookup works from left to right only. Wait..what is left to right?
What I mean is, the look up value parameters on the vlookup formula is the point of reference.
What I mean by point of reference, if the look up value is under column A such as A11, A12 or any cells on column A, then the column index number will start from Column A, Column B, Column C and so on and so for.
To simplify this explanation, let's take our example from above table.
Column A is Asset Number (column index number 1), Column B is Description (column index number 2), Column C is Owner (column index number 3) Let's say we have this formula
=Vlookup(B2,B2:C20,2,False), this formula will return "Kim".
If we have this formula =Vlookup(B2,B2:C20,3,False), it will return a #REF! error.
Why it will return an error? The formula is not able to find any data on column index number 3 as define in vlookup.
But we have 3 columns and column 3 is Owner, as explained above the lookup value is under column B which is B2 on our example.
Since column B is our point of reference, that will be considered by vlookup as column 1. That's why the formula =Vlookup(B2,B2:C20,2,False) has no error and correct value is returned.
And this formula =Vlookup(B2,B2:C20,3,False), will have error since it is not able to find any data.
Okay, that is quite a mouthful for Vlookup function.
If still confused, make some comment and let’s discuss on that.
Here's more example below to add confusion.
A vlookup formula of:
=Vlookup(A12,A2:C20,3,FALSE)
Returned value is:
Joachim
=Vlookup(A12,A2:C20,2,FALSE)
Returned value is:
Desktop
=Vlookup(A14,A2:C20,3,FALSE)
Returned value is:
Alfonso
=Vlookup(A14,A2:C20,2,FALSE)
Returned value is:
Laptop
=Vlookup(A7,A2:C20,3,FALSE)
Returned value is:
Matthew
=Vlookup(A7,A2:C20,2,FALSE)
Returned value is:
Ipad
A vlookup formula of:
=Vlookup(B7,A2:C20,3,FALSE)
Will result to an error of: #N/A
Why there is an error?
Look up Value is B7.
and the search range is define as A2:C20
=Vlookup(B7,B2:C20,3,FALSE)
will result to an error of #REF!
Why there is an error?
range value is 3
There is another good function, which is Hlookup but how it works is almost the same as Vlookup.
Vlookup formula is quite useful to find and locate data on Excel.
Table below will be used on how to demonstrate and use vlookup. But this basic idea is the same way you can apply even on a complicated or huge amount of excel data.
First we examine the parameters of Vlookup.
By typing Vlookup formula on Excel, Excel will prompt or display the parameters needed to complete the function.
Let's try to simplify or use lay man terms for the vlookup parameters.
In Excel 2007 that I'm using it displays these parameters below when I type Vlookup formula :
=Vlookup (look_up_value, table_array,col_index_num, [range_lookup])
The look_up_value is indeed the value that you're looking for or the data you're trying to find.
The table_array is the range of cells, or basically the whole cells or columns in which the data will be search.
The col_index_num, is the data that will be returned once the look_up_value matches any data define on the table_array.
The [range_lookup], let's just put a FALSE value on it to find the Exact Match.
Now that we know the parameters for the vlookup function, will use it to find some data on our example table below.
| A Column (Column 1) | B Column (Column 2) | C Column (Column 3) |
|---|---|---|
| Asset Number | Description | Owner |
| ABC-123 | Laptop | John |
| ABC-456 | Desktop | Kim |
| ABC-789 | Ipad | Thomas |
| ABC-1122 | Laptop | Martin |
| ABC-1455 | Desktop | Paul |
| ABC-1788 | Ipad | Matthew |
| ABC-2121 | Laptop | Jude |
| ABC-2454 | Android | Augustine |
| ABC-2787 | Ipad | Francis |
| ABC-3120 | Laptop | Jude |
| ABC-3453 | Desktop | Joachim |
| ABC-3786 | Android | Anthony |
| ABC-4119 | Laptop | Alfonso |
| ABC-4452 | Desktop | Lorenzo |
| ABC-4785 | Android | Pedro |
| ABC-5118 | Laptop | Michael |
| ABC-5451 | Desktop | Gabriel |
| ABC-5784 | Android | James |
| ABC-6117 | Laptop | Peter |
Let's say we know the asset number and we want to check who's the owner of it.
So in vlookup function, since we have the asset number it will be a good start to find some other data.
Vlookup function formula will be constructed like this:
=Vlookup(A11,A2:C20,3,False) will return Jude
=Vlookup(A12,A2:C20,3,False) will return Joachim
Let's examine the formula, the "A11" is the look up value for Asset Number ABC-3120, the "A2:C20" is the range of cells and column for the search area, the number "3" means return the value if the exact match is found, three columns from the look up value. First column is Asset Number, second column is Description, third column is Owner. Then the last parameter is "False" which tells vlookup to find for the exact match.
Now let's check the second vlookup formula, the value of "A12" is the look up value, A2:C20 is the search range area, number "3" is to return the value from the third column "Owner" if the exact match is found.
If the column index number in the vlookup formula was set to "2", then the second column labeled as "Description" will be the returned value for the vlookup.
You can try changing the vlookup formula to =Vlookup(A12, A2:C20,2,False), you will notice that returned value will be "Desktop", it is because you define on the formula, if there is an exact match get the value from second column.
You can try to experiment, changing the column index number and the look up value, but don't change the search range (don't change the search range if you copy the same table from this example) and don't change also the False parameter.
Vlookup is very useful to find and search data, but it will generate errors such as "#NA" and “#REF", I know you've seen those errors a lot and your eyes will turn big and rolls when such error occurs.
To avoid such error, we need to know how vlookup search for data. Vlookup works from left to right only. Wait..what is left to right?
What I mean is, the look up value parameters on the vlookup formula is the point of reference.
What I mean by point of reference, if the look up value is under column A such as A11, A12 or any cells on column A, then the column index number will start from Column A, Column B, Column C and so on and so for.
To simplify this explanation, let's take our example from above table.
Column A is Asset Number (column index number 1), Column B is Description (column index number 2), Column C is Owner (column index number 3) Let's say we have this formula
=Vlookup(B2,B2:C20,2,False), this formula will return "Kim".
If we have this formula =Vlookup(B2,B2:C20,3,False), it will return a #REF! error.
Why it will return an error? The formula is not able to find any data on column index number 3 as define in vlookup.
But we have 3 columns and column 3 is Owner, as explained above the lookup value is under column B which is B2 on our example.
Since column B is our point of reference, that will be considered by vlookup as column 1. That's why the formula =Vlookup(B2,B2:C20,2,False) has no error and correct value is returned.
And this formula =Vlookup(B2,B2:C20,3,False), will have error since it is not able to find any data.
Okay, that is quite a mouthful for Vlookup function.
If still confused, make some comment and let’s discuss on that.
Here's more example below to add confusion.
A vlookup formula of:
=Vlookup(A12,A2:C20,3,FALSE)
Returned value is:
Joachim
=Vlookup(A12,A2:C20,2,FALSE)
Returned value is:
Desktop
=Vlookup(A14,A2:C20,3,FALSE)
Returned value is:
Alfonso
=Vlookup(A14,A2:C20,2,FALSE)
Returned value is:
Laptop
=Vlookup(A7,A2:C20,3,FALSE)
Returned value is:
Matthew
=Vlookup(A7,A2:C20,2,FALSE)
Returned value is:
Ipad
A vlookup formula of:
=Vlookup(B7,A2:C20,3,FALSE)
Will result to an error of: #N/A
Why there is an error?
Look up Value is B7.
and the search range is define as A2:C20
=Vlookup(B7,B2:C20,3,FALSE)
will result to an error of #REF!
Why there is an error?
range value is 3
There is another good function, which is Hlookup but how it works is almost the same as Vlookup.
ok
ReplyDelete