How to Use the VLOOKUP Function for Dynamic References
The references used in the examples thus far have all been fixed to a specific cell or range of cells. That’s great and is often sufficient for your needs.
However, what if the cell you are referencing has the potential to change when new rows are inserted, or somebody sorts the list?
In those scenarios, you could not guarantee the value you want will still be in the same cell that you initially referenced.
An alternative in these scenarios is to use a lookup function within Excel to search for the value in a list. This makes it more durable against changes to the sheet.
In the following example, we use the VLOOKUP function to look up an employee on another sheet by their employee ID and then return their start date.
Below is the example list of employees.
The VLOOKUP function looks down the first column of a table and then returns information from a specified column to the right.
The following VLOOKUP function searches for the employee ID entered into cell A2 in the list shown above and returns the date joined from column 4 (fourth column of the table).
=VLOOKUP(A2,Employees!A:E,4,FALSE)
Below is an illustration of how this formula searches the list and returns the correct information.
The great thing about this VLOOKUP over the previous examples is that the employee will be found even if the list changes in order.
In this article, we have looked at multiple ways to cross-reference between Excel spreadsheets and workbooks. Choose the approach that works for your task at hand, and that you feel comfortable working with.
No comments:
Post a Comment