LOOKUP FORMULAS are used by Excel to retrieve a piece of information from a set of data held in the spreadsheet. In general the elements of these formulas are
- what to look for (hence ‘lookup’)
- where the data is – normally a range of cells
Lookup formulas are one of the most common tasks in Excel :
- Some Lookup formulas are relatively simple such as VLOOKUP() or HLOOKUP(). These tend to be the first type of lookup formulas that most people come across when using excel. However, these formulas have some limits and there is often a need for more flexibility
- Most advanced users of excel find that formulas such as INDEX() provide the flexibility they require. This flexibility increases significantly when used in combination with MATCH() and INDIRECT(). OFFSET() is another flexible lookup formula but is less used than INDEX(). However it does have a special use that converts a named range from a fixed range into one that varies with the size of the data it is referring to.
Lookups are generally used in two situations:
- To add a descriptive field to a downloaded set of data. The descriptive name may well come from a second download e.g. to add a name field to the current periods payroll run
- To produce a small summary of a particular record in a data set. This would normally be based on an entry in a particular cell that you can change to update the results. If this variable entry is text then it is always best to use data validation of a drop down box to ensure the correct spelling.
NB Data can be of two main types: Fixed or variable:
- Fixed data changes infrequently – may be once a year or as the circumstances of the business change e.g. a new product is launched
- Variable data is refreshed frequently e.g. on a weekly or monthly basis.
- For example: With payroll data the list of employee names and departments they work in will tend to be fixed whereas there pay and deductions each period will change i.e. it is variable
Excel Monkey 