In this week’s tip we’ll show you how to look up unit prices in your workbook using the LOOKUP function in Excel. The LOOKUP function is an alternative method to the more commonly used VLOOKUP function in Microsoft® Excel®.
Manually entering data in your workbook can lead to inaccuracies with your information, you can apply the LOOKUP function to avoid that.
The LOOKUP function can be used to lookup data in single row or column range, it searches for the value in the first row or column and returns a value from the same position in the second row or column range selected. The data being searched will be sorted in ascending order.
In our example, we use the LOOKUP function, using the vector form method, to return the unit price for items
Note: Download the workbook to practice.
1. Place the cursor in cell C4. Select the Formulas Tab. Then select Lookup and Reference.
2. From the drop down menu select LOOKUP then OK.
(NB: By default the Vector from will be selected)
3. Place the cursor in the Lookup_value box and press F3. Select the defined name (ITEMS) for the range B4:B10.
(N.B: For a tutorial on how to define names or create names, click here.)
4. Place the cursor in the Lookup_vector box and press F3. Select the defined name (ITEMS1) for the range H3:M3.
5. Place the cursor in the Result_vector box and press F3.Select the defined name (UNIT_PRICE) for the range H4:M4. Select OK
6. Copy the formula down to cell C10.
The unit prices have been looked up without having to manually enter them. This will ensure that your data is accurate, whilst saving you time and making your process more efficient.
