This is set to true when you are looking for an approximate match and set at false when looking for an exact match. In this example, 2 is chosen and the only possible piece of data that can be displayed. The column index needs to be column 2,3,4, or any column with data that you need to display that is past column 1. Note that the column being referenced for the lookup value is always column 1. This is the column that will display data in where the function is inputted. This is all the data in the lookup table excluding the column headers. The table array is the table where the data is being looked up. The cell E6 is chosen because it is the salary index for John Sherwood in the first record. This is going to be the salary index for this record. Details on each parameter are listed below. Diagram of the VLOOKUP FunctionĮach parameter of the function needs to be entered into this next window. A false statement will make an exact match when referencing the lookup table, and a true statement will look for an approximate match. The range lookup is going to be stated as a false or true statement. This may be a little difficult to understand, but when you see it in an example, everything will make sense. So, if the lookup value in located in the column index, then data from another part of the data can be returned. This is the column that contains that value that you want to match to return another value. The next value in the function column in index. This value may be referred to as the lookup range or the lookup table. This table contains lookup value and any data that may be returned for the VLOOKUP function. When the value is found Excel will return any data that lies in a column to the right of this data. The lookup value can be a number, reference or formula. This is the value that Excel is going to search for in the range.
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) The structure of the VLOOKUP formula is shown below: The HLOOKUP has the same premise except it references information in the first row of the array to display information found in other rows. A simplified illustration of the function process can be seen below.Īnother function that is like the VLOOKUP function is the HLOOKUP function. After a match is made, data is selected from a column that is preselected in the function and is returned to display in the cell that contains the function. So, when this function is used a piece of selected data from a column (vertically aligned data) is referenced. The VLOOKUP function is very useful as it can display information by using a reference value in the function. Don’t be alarmed if you are a beginner because using VLOOKUP in this example is very basic. This tutorial is conducted in Excel 2016 but applies to version Excel 2007 and higher.
#Formula for vlookup in excel 2016 how to#
In this article, I give some basic instruction on how to use this function as a lookup/reference tool for displaying information. The VLOOKUP function is a powerful Microsoft Office tool and can be utilized for many applications.
0 kommentar(er)
