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.
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
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.
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 ‘Apples’ on row 1 of the range.
If the Match Type is changed to -1 (greater than) then the value of 6 will be returned as the next instance of ‘Apples’ is on row 6 (cell A6). Interestingly, this only works when wildcard search values are used within the search string.
The search text value is not case sensitive.
If you find this article is helpful and you want to help others too, just share it in any social media (such as Facebook, LinkedIn).
Got it Pass Team is happy to work with Richard in presenting this article.
About Richard Shepherd-
Richard is an expert in MS Excel and Access with extensive experience of developing MI reporting systems, databases and dashboards using VBA or RAD for major companies such as Santander, Nationwide Building Society and Zurich Insurance. He is also the author of Access 2010 VBA Macro Programming and Excel VBA Macro Programming. He was graduated in University of the Thames Valley, United Kingdom.
Note: Some of the links in this page contain affiliate links. I will earn a small commission for any purchases you make, though there is no extra cost to you.
The products or services I recommend only I use them myself or after my investigation and truly believe they are useful to you. Don’t forget there are many other options out there – these are simply my preferred choices.