Microsoft Excel Training Videos

Vlookup using lists in Excel 2007

Vlookup was described earlier. Today we will learn how to use Vlookup with lists. You can enter data and perform calculations automatically using vlookup and lists. The list is created in a seperate part of the worksheet or in another worksheet.
The step by step process is described below:
  • Enter your data like employee name and grade.
  • Next create an appropriate list like the grade and the relevant matching salary
  • Now use the Vlookup function to 'extract' or 'lookup' a value as shown in the video.
  • Notice that the autofill does not work if you use the relative cell reference. Therefore the table array from which we lookup a value must be accessed using an absolute cell reference.
You could have used the multiple 'IF' function to solve the problem but multiple 'IFs' can be used upto 9 times in Excel 2003 and 32 times in Excel 2007. Therefore, if your lookup list is large, say 50 different items, then there could be a problem.


Vlookup using lists