VLOOKUP bug?
 

No doubt and hopefully this isn't a bug but I just can't see what is
wrong?! I have the following formula in a worksheet :
=VLOOKUP(MIN(C4:C204),$C$4:$E$204,3). This formula worked fine but
then I made a copy of the worksheet to insert different data. With the
new data, the formula returns #N/A (obviously this hasn't affected the
old worksheet). I have traced the problem back to something strange
with the data; the min value is 1.40653205 (this is the 102nd entry)
but if I change this to a larger number so that the 101st entry is the
min, the formula works. The original worksheet has the min as the
101st value. This isn't the end of it though. With some other data
that has the min value in the 100th entry it still doesn't work? I've
tried to put the worksheet in a new workbook and also tried changing
the formula to =VLOOKUP(1.40642214,$C$4:$E$204,3). If I change the
formula to =VLOOKUP(1.999,$C$4:$E$204,3) or a larger number, it works!
So now I just can't understand why it works with the original worksheet
data. I've also tried putting this data into my new workbook and it
does work. WHAT THE!!!


--
shmish111
------------------------------------------------------------------------
shmish111's Profile: http://www.excelforum.com/member.php...o&userid=30449
View this thread: http://www.excelforum.com/showthread...hreadid=501123

VLOOKUP bug?
 

Should it be

=VLOOKUP(MIN(C4:C204),$C$4:$E$204,3,False)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"shmish111" wrote
in message ...

No doubt and hopefully this isn't a bug but I just can't see what is
wrong?! I have the following formula in a worksheet :
=VLOOKUP(MIN(C4:C204),$C$4:$E$204,3). This formula worked fine but
then I made a copy of the worksheet to insert different data. With the
new data, the formula returns #N/A (obviously this hasn't affected the
old worksheet). I have traced the problem back to something strange
with the data; the min value is 1.40653205 (this is the 102nd entry)
but if I change this to a larger number so that the 101st entry is the
min, the formula works. The original worksheet has the min as the
101st value. This isn't the end of it though. With some other data
that has the min value in the 100th entry it still doesn't work? I've
tried to put the worksheet in a new workbook and also tried changing
the formula to =VLOOKUP(1.40642214,$C$4:$E$204,3). If I change the
formula to =VLOOKUP(1.999,$C$4:$E$204,3) or a larger number, it works!
So now I just can't understand why it works with the original worksheet
data. I've also tried putting this data into my new workbook and it
does work. WHAT THE!!!


--
shmish111
------------------------------------------------------------------------
shmish111's Profile:
http://www.excelforum.com/member.php...o&userid=30449
View this thread: http://www.excelforum.com/showthread...hreadid=501123

VLOOKUP bug?
 

shmish,
You are using VLOOKUP() for inexact match. I.e. the 4th argument is
missing, therefore its default value is used, which is TRUE. When this
is the case VLOOKUP() works correctly if the data in the first column
(C2:c204 in this case) are sorted.

Using FALSE as a 4th argument will match the MIN() value, since it is a
member of the array. In this case you do not need to sort. But if you
want other matches it will not work.

Does this help?
Kostis Vezerides

VLOOKUP bug?
 

Beutifull thankyou. :)


--
shmish111
------------------------------------------------------------------------
shmish111's Profile: http://www.excelforum.com/member.php...o&userid=30449
View this thread: http://www.excelforum.com/showthread...hreadid=501123