Excel VLOOKUP ISNA and MATCH spreadsheet functions

The VLOOKUP function is commonly used to get details from a table of data. A specified value is looked for in the first column and a value from an adjacent column is returned. =VLOOKUP(SearchItem, TableRange, ColumnNo, Exact). For example you may search for a surname in a list and then return a phone number from another cell on the same row.

A study by accountants Coopers and Lybrand has shown that 90% of spreadsheets with more than 150 rows contain errors. Check out my information on creating well designed spreadsheets.

The SearchItem represents the item being sought and can either be an absolute value (eg 234 or "Fish") or a cell reference. The TableRange represents the range of the whole table (excluding headings) where the left most column contains the values being searched. The ColumnNo specifies which adjacent column contains the value to be returned if the search is successful (where the left most column is 1). The Exact argument is important. If ignored or set to 1, the function will assume that the data is sorted in ascending order and the next value will be returned if the SearchItem can not be found. In the example below a search for XN44 would fail but will return a value adjacent to the next highest item i.e. XN46. If set to zero an exact match must be achieved or else an error (#N/A) will result. A value of zero therefore means that the data does not need to be sorted in ascending order.

In some situations the match must be exact and an error will spoil the functioning of the worksheet. Avoiding this problem requires a combination of different functions in a complex formula. It is possible to test whether an exact match can be found using the MATCH function. =MATCH(SearchItem, ColumnRange, Exact) If no match is found, an error will again appear but this can be tested for using the ISNA() function. ISNA() will return a True value if the formula it is looking at results in an #N/A error. These functions could be combined with IF() to return either an exact match or zero.

In the example a user can enter a Grade Code in cell D10. In cell D11 the value is checked to see whether an exact match is found and an appropriate message returned. In cell D12 the salary for the selected grade is multiplied by the number of staff and the employers 'on costs'. In this case it is essential to know that the calculation is not based on an approximate match with another grade.

Click to open this PDF document

IF(), ISNA() and VLOOKUP() or MATCH() can all be combined into a single safe formula for a lookup. The following formula will return a salary for a grade or zero if the grade code is not found: =IF(ISNA(MATCH("XN51", B5:B7, 0)), 0, VLOOKUP("XN51", B5:D7, 2, 0)). Note that the MATCH() element of the equation could be replaced with a repetition of the VLOOKUP function which will produce the same #N/A error if the exact value isn't found.

Using MATCH to find multiple items

The previous example used the function to find a single item in a list. It can also be used as an array function to find the row containing various items. e.g. Ward 3 in column K and also NonPay in column L.

The formula in cell N7: {=MATCH(1,(K13:K18=L7)*(L13:L18=L8),0)} is an array function which must be entered using <Ctrl> + <Shift> + <Enter>. Having identified the row containing the data, it is possible to construct a formula to add up the values in periods 1 to 3.

VLOOKUP() can be used to return a value relating to a single record. If you wish to SUM() or COUNT() one or more records which meet a particular condition (or search item) you can use other functions such as arrays or SUMPRODUCT().

Home	Excel	Access
\| Feedback \| IF function \|

Excel Functions: Vlookup, Isna, Match

Using MATCH to find multiple items