Excel Video 63 introduces the difference between exact and approximate matches in the VLOOKUP formula. You’re familiar now with the first three fields in VLOOKUP, what to look for, where is the data table, and what column of information do you want to look in. The fourth variable in VLOOKUP is optional, but very important. The fourth variable tells Excel whether you want an exact match (if Excel can’t find the exact thing you’re searching for, put an #N/A error message in the cell) or an approximate match (if Excel can’t find an exact match, the next largest value that is less than the value you’re looking for is returned in the cell.) It’s the Price Is Right approach to looking for your data, the closest to your value without going over.
Excel defaults to TRUE, meaning if Excel can’t find an exact match, it will give you the largest value that is less than the value you’re looking for. For this to work, if you choose to make the fourth variable TRUE (or omit the fourth variable), you need sort your data in ascending order. Otherwise, Excel may not give you the closest value without going over the number you’re searching for.
Watch Excel Video 63 for the new formula IFERROR. IFERROR takes the formula you enter and if the formula results in a valid answer, Excel displays the answer. If the formula results in an error, you tell IFERROR what to put in the cell besides the error message. You’ll find IFERROR helpful if you’re looking for numbers and want to total all of your answers. Having an #N/A error will screw up your totals, so put a 0 in the IFERROR formula instead so that Excel can still total your answers. You can also put a more user friendly error message (remember to put text entries in quotes) instead of Excel’s default error message, as we do in this example.
VLOOKUP is a very powerful function. Understanding the difference between exact and approximate matches and using IFERROR will make VLOOKUP even more useful.
