VLOOKUP is a function within excel which can look at a value in one column and find the matching value in another column. A VLOOKUP is incredibly useful when working with several tables that have some common data but is incomplete without data from another table.
This tutorial will show you how to use the VLOOKUP function.
- Create or have ready two tables, both tables should have one unique column which is present in both tables such as Staff ID.
- The VLOOKUP formula is = VLOOKUP(lookup_array,table_array,col_index_num, [range_lookup])
- Lookup_array – This is the data that is unique and common in both tables. In this example, this is the staff ID from table A. Finish by typing a comma to close the current function.
- Table_ array – This is asking where is the table that we want to use to look up data from is located. For this example, we will select table B and type comma to move on to the next function.
- We also need to add a dollar sign in front of the rows and columns of the table that we have selected to make it absolute so that when we drag the formula down later the table range does not change.
- Col_ind_num – this is asking which column the data we want to call is in, we will type 2 since in this example the bonus details are in the 2nd column of table B. Finish off by typing a comma to close the function.
- Finally, we need to choose the range_lookup, this is asking us if we want an exact match or a close match. As we want an exact match with the staff ID we will type False or 0 and close bracket.
- Now that we are done we can click enter and this should give us the correct bonus for the first entry.
- We now need to drag the formula all the way down to complete this exercise.