┬Ā
Search

# Excel for Preparing Financial Reports: Lookup, Index and Match Functions

These important Excel functions are very useful to accountants in preparing financial reports. They allow enormous flexibility in terms of searching blocks of tabular data so that specific items can be extracted and used within a report.

Large chunks of tabular financial data are often imported into Excel as the basis of financial reports. Data is often downloaded from accounting systems, often in the form of a CSV (comma separated values) file and then imported into Excel. The problem is that the information required for a report can be all over the place and it is no apparent what needs to go where in a financial report. The imported data is frequently in a different order from one month to the next, and what was at a specific cell location for one import, may totally change on a further import.

These functions help you to locate the required information so that it can be added into the correct place within a report.

## LOOKUP

The function uses three parameters:

• Lookup Value - The value to search for ŌĆō this could be a text string or a cell reference

• Lookup Vector ŌĆō The one row or one column range to search

• Result Vector ŌĆō The one row or one column range of results

Lookup will perform an approximate match on the range specified by the Lookup Vector where the lookup value is numeric. It is important to realise that an approximate match may not necessarily produce the correct result.

In the example below, there is a two-column list of product and quantity. Using the LOOKUP function in cell D2, the lookup value is ŌĆśApplesŌĆÖ, the lookup range is A2:A6, and the range where the result is taken from is B2:B6

This gives the result of 168. Note that ŌĆśApplesŌĆÖ appears further down in the list, but the function will only pick up the first instance found.

If no result is found then the value #N/A will be returned

The range specified for the lookup and result do not have to be in the order of the columns. In the example below the lookup range is B2:B7 and the result range is A2:A7

The lookup value is 168, and it returns ŌĆśApplesŌĆÖ. Because the lookup function uses an approximate match, entering the lookup value of 236 will still return a value of ŌĆśApplesŌĆÖ. Not until the lookup up value of 237 is entered will a value of ŌĆśPearsŌĆÖ be returned. It always matches the next smallest value. When the lookup value is greater than all the values, the function will return the last value. When the lookup value is less than all the values, the function will return #N/A

The result range must be the same size as the lookup range. The function is not case sensitive

The Lookup function does not support wildcards (*?) within a text string

## VLOOKUP (Vertical Lookup)

Vlookup is related to Lookup, but has the advantage of being able to do exact and approximate matches on numeric values. This is a very important difference as shown in the example below.

Vlookup only searches the first column in a tabular range (known as column 1).

Vlookup has four parameters:

• Lookup value ŌĆō can be text or numeric and can also be a cell reference

• Lookup range ŌĆō tabular range to be searched

• Result column ŌĆō number of columns within the tabular range (first column is number 1)

• Approximate or exact match ŌĆō optional ŌĆō default id approximate. Only works for numeric values. False value is exact match

In the example below, the first column (A) is quantity and the second column (B) is the product name.

The function has exact match selected so the value of 168 will return ŌĆśApplesŌĆÖ. ŌĆśApples is also repeated on row 7, so if we use the lookup value of 239 then the exact match will also return ŌĆśApplesŌĆÖ. Note that the original Lookup function only does approximate matches so it would return ŌĆśPearsŌĆÖ in this case.

However, if we keep the lookup value of 239, but change the match parameter to true (approximate match), then the value ŌĆśPearsŌĆÖ will be returned. There is also a function called HLOOKUP (Horizontal Lookup). This works in the same way as VLOOKUP except that it searches through the first row in a range instead of the first column

These two functions do not support wildcards (*?) within a text lookup string

## INDEX

The index function will give the value found at an intersection of a row and column number within a defined array.

The Index function has three parameters:

• Range ŌĆō A multi-column, multi-row range of tabular data

• Row number ŌĆō the row number within the defined range where the desired value is to be found.

• Column number ŌĆō the column number within the defined range where the desired value is to be found

The row and column numbers begin at a value of 1

The Index function does no searching of values, unlike the Lookup functions. It simply returns a value defined by the row and column number within the specified range.

In the example below, the row number is 4 and the column is 2. The range is B4:D10. This equates to cell C7 which has a value of 633

Note that the range specified includes the column headers, so the row and column numbers are offset from cell B4, which is the top-left cell of the range. ## MATCH

The Match function operates on a single column range and locates the row number of the search value. It is often used with the Index function to retrieve a value on a certain row, when the column in the tabular range is already known.

The Match function has three parameters:

• Lookup Value ŌĆō Can be text or numeric

• Lookup Range ŌĆō must be a single column within a range

• Lookup Type ŌĆō Optional ŌĆō Exact match, or greater than or less than. Default is Exact MatchŌĆÖ In the example above, the search text is ŌĆśApplesŌĆÖ and the first instance is at cell A2. The function returns 2 which is the row number within the search column

Wildcards (*?) can be used if the lookup value is a text string In this example the search text uses a wildcard string ŌĆśAppl*ŌĆÖ. This finds the first instance of ŌĆ