Lookup 'near' minimum value

[Zipplewrath]

I think I've found a bug/short coming in the Vlookup function. It appears that if the number (A) is small (less than 0.04) it will "match" to a z of zero. This is all when using the "near" modifier. I've "fixed" my problem by merely multiplying the A and the z by 1000. However, there seems to be no direct way to establish what constitutes "near".

[TomGutman]

What is the "near" modifier? I don't see any reference to such in the help. The help says that the compare is based on TOL.
__________________
� � � � Tom Gutman

[Zipplewrath]

Vlookup(z, A, c, modifier) Takes a real input z, an array A of mixed real, complex, or string values, and a comparison criterion modifier. Vlookup matches z in the first column of A, subject to the conditions of modifier, and returns the result(s) in column c in the same rows as the matched elements.

z must be a scalar, unless you specify "range" as the comparison criterion, in which case it can be a 2-element column vector.

Comparison Modifiers for Match and Lookup Functions

The following modifiers are supported by the Match and lookup functions provided as part of the Data Analysis Extension Pack:

Comparison
Meaning

"near"
Returns the value closest to z.

"gt"
Matches everything greater than the value z.

"lt"
Matches everything less than the value z.

"geq"
Matches everything greater than or equal to z.

"leq"
Matches everything less than or equal to z.

"not"
Matches everything not equal to z.

"range"
Matches everything in the specified range. z must be a two element vector containing the upper and lower bounds of the range.

[RichardJ]

On 2/1/2010 2:37:14 PM, Zipplewrath wrote:
>Vlookup(z, A, c, modifier)
>Takes a real input z, an array
>A of mixed real, complex, or
>string values, and a
>comparison criterion modifier.
>Vlookup matches z in the first
>column of A, subject to the
>conditions of modifier, and
>returns the result(s) in
>column c in the same rows as
>the matched elements.

The one from the DAEP. I had forgotten about that. It says it returns the nearest value, not that it returns a near (whatever that would mean) value.

Richard

[ptc-1368288]

The boolean modifier is entered as a string, which can have the following values:

"near" - returns the value closest to z
"gt" - matches everything greater than the value z
"lt" - matches everything less than the value z
"geq" - matches everything greater than or equal to z
"leq" - matches everything less than or equal to z.
"not" - matches everything not equal to z.
"range" - matches everything in the given range specified as a two element matrix instead of the scalar z.
_______________________

For more accurate answer, the Locate function is an alternative. Usually another technique is to vectorize over the bolean. This is shown is many of my recent posts. Browse the collab.

jmG

[ptc-1368288]

jmG

[ptc-1368288]

On 2/1/2010 2:06:51 PM, Zipplewrath wrote:
>I think I've found a bug/short
>coming in the Vlookup
>function. It appears that if
>the number (A) is small (less
>than 0.04) it will "match" to
>a z of zero. This is all when
>using the "near" modifier.
>I've "fixed" my problem by
>merely multiplying the A and
>the z by 1000. However, there
>seems to be no direct way to
>establish what constitutes
>"near".
_______________________________

Pass your data table in a Mathcad sheet.
"Save as" 11 or lower for a larger audience.
There are many ways to select data from table.
What version are you using ?
Make sure you abstract the project !

jmG