VLookup prob

Posted by Amroo on April 20, 2001 7:53 AM

Bonjour,
on my sheet1 I have a range filled as this
A
B
R
E
D
On my sheet2 I have a table as this
A 1
B 3
C 5
D 7
When I want to Vlookup the value of sheet1 I enter
for lookup_value = A1 (of sheet1)
and Table_array =Sheet2!$A$1:$B$4
Col_index=2
Then I got that on sheet1
A----1
B----3
R----7 (or I guess to get #N/A or #Value)
E----7 "" "" "" "" ""
C----5
So what happen to my Vlookup procedure?
Thank you for the help.
Amroo

Posted by Mark W. on April 20, 2001 7:58 AM

The last (optional) argument to your VLOOKUP()
function was omitted, set to 1, TRUE. As stated
in the Help topic for VLOOKUP, "If TRUE or omitted,
an approximate match is returned. In other words,
if an exact match is not found, the next largest
value that is less than lookup_value is returned."

Posted by Dave Hawley on April 20, 2001 8:08 AM

Hi Amroo

VLOOKUP takes another agument "Range_Lookup" if this is set to False it looks for an exact match. If set to True (or omitted) it will return the closest match.

So in your case if Set to True (or ommitted) it will use "D" on sheet 2 for it's return value with "R" and "E" ( as they are the closest match)

If you set it to False it wont find "R" or "E" as there is no exact match, so it returns "#N/A" (not available)

Dave

OzGrid Business Applications