|Feedback | IF function ||
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.
Would you like to learn more?
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.
|[D11] =IF(ISNA(MATCH(D10, B5:B7, 0)), D10 & " is NOT on the Table", D10 & " Found")|
|[D12] =VLOOKUP(D10, B5:D7, 2, 0) * VLOOKUP(D10, B5:D7, 3, 0) * (1+D8)|
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.
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().
|Get this information as a document
accompanied by Excel worksheets
|Click here for details about
file. It has been rewritten for Excel 2010.
|file: xlvlookupmatch.htm||© meadinkent.co.uk 2013||Page last updated Dec11||CMIDX S2 P2 Y|