Hello Excel-lent,
Let us start our session today with the VLOOKUP Function, basically because it is the most used of the lookup and Reference functions.
How the VLOOKUP Function Works
Excel's VLOOKUP function, which stands for "vertical lookup", can be used to look up specific information located in a table of data or database. VLOOKUP normally returns a single field of data as its output. How it does this is:
You provide a name or Lookup _value that tells VLOOKUP in which row or record of the data table to look for the desired information. You supply the column number - known as the Col_index_num - of the data you seek.
The function looks for the Lookup _value in the first column of the data table VLOOKUP then locates and returns the information you seek from another field of the same record using the supplied column number.
The VLOOKUP Function's Syntax and Arguments
A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.
The syntax for the VLOOKUP function is:
= VLOOKUP (lookup_value, Table_array, Col_index_num, Range_lookup)
Lookup _value - (required) the value you want to find in the first column of the Table_array
Table_array - (required) this is the table of data that VLOOKUP searches to find the information you are after
- The Table_array must contain at least two columns of data
- The first column normally contains the lookup_value
Col_index_num - (required) the column number of the value you want found
- The numbering begins with the Lookup_value column as column 1
- If Col_index_num is set to a number greater than the number of columns selected in the range argument a #REF! error is returned by the function
Range_lookup - (optional) indicates whether or not the range is sorted in ascending order
- The data in the first column is used as the sort key
- A Boolean value - TRUE or FALSE are the only acceptable values
- If omitted, the value is set to TRUE by default
- If set to TRUE or omitted and an exact match for the Lookup _value is not found, the nearest match that is smaller in size or value is used as the search_key
- If set to TRUE or omitted and the first column of the range is not sorted in ascending order, an incorrect result might occur
- If set to FALSE, VLOOKUP only accepts an exact match for the Lookup _value
Let us discuss the diagram below.
The table in the diagram above represents a company's employee database which shows employees Last Name, First Name, Department, Telephone Extension and Date Hired.
Given the Last Name only, you are required to find a staff's First Name, Department, Extension and Date Hired. Though Control + F button can help you locate one field at a time for less than 5 staffs conveniently but when you have a database of over 2,000 staffs, you are better off VLOOKUP.
Don't miss out on the sessions, Join Excelgist today to receive excel discussions straight in your mail box and do share this post with your colleagues.
Excel with Excel!
Excel-lently yours,
Oladapo Sorinola
07014282477, 07062932708
BB Pin 52E9802D
No comments:
Post a Comment