To avoid this issue you can use the range_lookup field to tell Excel to find only an Exact match.
If you leave the range_lookup field empty, Excel will find the nearest match, meaning that employees who have been employed for 2, 4, 6 and 8 years will still receive a bonus when they should not. Year 2, 4, 6 and 8 are not listed in the table as employees do not receive a bonus in these years. Info: You will notice in our table_array that only the years where an employee will receive a bonus are listed. This field determines if you want Excel to find you the closest match or an exact match. Now move your cursor to the range_lookup field.Enter the value 2 into the Col_index_num field.If you do not have an existing file, create a sample file such as the one shown below:.If you wish to use an existing file which contains a table of data suitable for this exercise, press Ctrl + F12, the Open dialog box will appear allowing you to locate the file and click Open.To create an Excel vlookup function, follow these steps: This is where the Vlookup is going to save me ALOT of time. Imagine now if I had 500 employees which I needed to perform this task for – it would be very time consuming if I did this manually. In this example I’m only looking at 6 staff members so technically I could manually look these up myself. In our first example, we are using an employee bonus scheduled where want to Excel to look at the number of years each member has been employed, then go to our bonus schedule, which is laid out in a vertical format, and identify how much their bonus is based on their years of employment. The range_lookup tells Excel to find the closest match, rather than looking for exact matches. The col_index_number is the column number within the table where the answer is being returned from. The table_array is the location of the data containing the answer you wish to lookup. The lookup value is the cell reference containing the value you wish to lookup. =VLOOKUP(lookup_value, table_array, col_index_number, ) Lookup_Value We will look at the VLOOKUP function which is shown below: There are two types of lookup functions: VLOOKUP (used for vertically presented data) and HLOOKUP (used for horizontally presented data). SyntaxĪn important aspect of understanding any function in Excel is the ability to identify the parameters or syntax required. For those who have not heard of the lookup function, this post will provide you with a great introduction to these two functions and how they can be used to save time. It is a perfect example of how Excel can “do the work for you”. The Lookup Function is a fantastic time-saver. This post is going to solve any confusion you have about using either lookup function. Many people have heard about the Excel vlookup and hvlookup functions but have unsuccessfully attempted to use them.
0 kommentar(er)
