| 5. Abandon the Exact Match argument to get a “fuzzy” match.
The fourth argument in the VLOOKUP function is to add “FALSE” which tells Excel to look for an exact match.
Occasionally, you might be trying to find the nearest value within a range of values. Say that your commission plan pays a base rate of one percent. Sales over $5,000 earn 1.1 percent. Sales over $12,500 earn 1.25 percent. Sales over $18,000 earn 1.35 percent. You could set up a special version of a lookup table with the lower limit of each range specified in the first column:
You would then change the fourth parameter of the VLOOKUP to TRUE to allow a range lookup. Excel will select the lower value in the range (so for a value of $10,000, it will match to the $5,000 row).
This approach can be used for names, addresses and Excel will do its best to find the closest matching record, just like it did in the range example above.
VLOOKUP is one of Excel’s most powerful features as it allows for less data entry by joining multiple sheets worth of information. Try it the next time you need to compare multiple sheets together or simply to save some data entry.
Dostları ilə paylaş: |
