h1

RETURNING A VALUE WITHIN A RANGE OF VALUES

October 5, 2010

Everyone knows how to return a value from a list where a matching value is found in a corresponding list; we use a vlookup function. Well with 2 tweaks a vlookup can also return a value where the matching value falls within a range of values, like in the case of a tax table or sales commission table.vlookup - approx. match

The following points are critical to the successful use of this technique:

1) If range_lookup parameter (last parameter) is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value (first parameter) is returned.

2) If range_lookup is either TRUE or is omitted, the values in the first column of table_array (second parameter) must be placed in ascending sort order; otherwise, vlookup might not return the correct value

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: