While working with Excel, quiet often you will feel the need the search a table in excel and get some value from it. There are few in-built functions (LOOKUP, HLOOKUP and VLOOKUP) provided by Excel to serve those needs. Each one of these function have their own usability. I will be covering VLOOKUP in this tutorial
VLOOKUP should be used when the table you are going to look-up is organised vertically like the one below
Lets assume you wish to search this table using "Employee ID" and get the name of that employee. You can make use of VLOOKUP function to do so. Following is the syntax of the function and definition of the parameters
VLOOKUP( lookup_value, table_array, row_index_number, [range_lookup] )
lookup_value: is the value to search for in the first column of the table_array.
table_array: is two or more columns of data which will be looked upon. If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order; otherwise, VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
col_index_number: is the column number in table_array from which the matching value must be returned. The first column is 1.
[range_lookup]: It is an optional parameter with a default value of "true". If passed as "false", the function will look for an exact match of lookup_value in table_array. If "true" or omitted, the function will look for an approximate match. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned.
Let back to where we started and let me explain these with the help of a few examples. Lets assume you want to look up employee id in cell A7 and get the name in B7. Type the following formula in B7
=VLOOKUP(A7,A2:C5,2,FALSE)
and hit enter, you will get the employee name in cell B7.
Other examples of how VLOOKUP works.
VLOOKUP(A7,A2:C5,2,TRUE) will return "Employee 3" when value of A7 is 11130
VLOOKUP(A7,A2:C5,2,FALSE) will return "#N/A" when value of A7 is 11130. Other possible errors returned by this function are explained below with their reason.
VLOOKUP(A7,A2:C5,2,FALSE) will return "Technical" when value of A7 is 11129.
Lets assume you wish to search this table using "Employee ID" and get the name of that employee. You can make use of VLOOKUP function to do so. Following is the syntax of the function and definition of the parameters
VLOOKUP( lookup_value, table_array, row_index_number, [range_lookup] )
lookup_value: is the value to search for in the first column of the table_array.
table_array: is two or more columns of data which will be looked upon. If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order; otherwise, VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
col_index_number: is the column number in table_array from which the matching value must be returned. The first column is 1.
[range_lookup]: It is an optional parameter with a default value of "true". If passed as "false", the function will look for an exact match of lookup_value in table_array. If "true" or omitted, the function will look for an approximate match. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned.
Let back to where we started and let me explain these with the help of a few examples. Lets assume you want to look up employee id in cell A7 and get the name in B7. Type the following formula in B7
=VLOOKUP(A7,A2:C5,2,FALSE)
and hit enter, you will get the employee name in cell B7.
Other examples of how VLOOKUP works.
VLOOKUP(A7,A2:C5,2,TRUE) will return "Employee 3" when value of A7 is 11130
VLOOKUP(A7,A2:C5,2,FALSE) will return "#N/A" when value of A7 is 11130. Other possible errors returned by this function are explained below with their reason.
VLOOKUP(A7,A2:C5,2,FALSE) will return "Technical" when value of A7 is 11129.
Possible Error returns from VLOOKUP
- If col_index_number is less than 1, theVLOOKUP function will return #VALUE!.
- If col_index_number is greater than the number of columns in table_array, the VLOOKUP function will return #REF!.
- If you enter FALSE for the not_exact_match parameter and no exact match is found, then the VLOOKUP function will return #N/A.
0 comments:
Post a Comment