Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array - As explained by Microsoft..
Okay in this example, I am going to use employee details and again, keep it very simple..
We have a spreadsheet with the employee details as shown below:
In another sheet or even spreadsheet, we want to get the phone number for one of the employees based only on their Employee ID number....
There are a few simple steps to this:
Okay in this example, I am going to use employee details and again, keep it very simple..
We have a spreadsheet with the employee details as shown below:
In another sheet or even spreadsheet, we want to get the phone number for one of the employees based only on their Employee ID number....
There are a few simple steps to this:
- First off, create a list based on EmpID as seen in the Blog - Drop Down List
- Now click on the cell you want to be auto-populated (we are going to add the first name, last name and phone number, so this will be repeated in 3 cells). If you have read this far, your probably best using the formula wizard found under Look Up & Reference in the Formulas Tab as below (Excel 2010).
- A window will appear asking you input the arguments for the function as below
A quick explanation of this window:
- The Lookup_value is the cell containing the list.. We are using EmpID, click in the text box and then click on the cell containing the list.
- Table_array, this is the area of the sheet that contains the info you want returned.. Again, click on the text box and then highlight the cells containing the required info, first name, last name and phone number.. Don't worry if you have to include other cells, in our example we have to include StartDate and Dept..
- Col_index_num, this is the index of the column that will return the requested data. Our example has last name, then first name in the original data set, but we want to show the first name followed by the last name, then the phone number, so enter 3 here for the third column which contains the first name.
- Range_Lookup, this is a logical - True or False argument, where False equals that you want an exact match returned and True means that you want the closest match returned..
Now follow the above steps for the last name and phone number in your desired cells, most probably next to the first name..
Before you use the formula wizard for all three, read below, it is faster either writing the formula or copy and paste, you will only need to adjust the Col_index_num for last name and phone number..
Remember last name was first in the original data set, so we used 3 as the column index for first name, now use 2 as the column index for the last name, and you have changed the order of the way the name is displayed..
The Formula
If you are like me, and would rather input the formula here it is:
=VLOOKUP(B3,Sheet1!A2:F5,3,FALSE)
Note the 4 arguments:
Lookup_value = B3
Table_array = Sheet1!A2:F5 (the data set is on Sheet1 and is held in cells A2 to F5)
Col_index_num = 3 (this is the first name column)
Range_Lookup = False (we want an exact match)
There is also hlookup, used for rows...
