problem with LOOKUP function
 

There is a problem with the Excel LOOKUP function that I would like to have
fixed. It doesn't appear to work with real numbers correctly. I set up a
column of fractional numbers and and used LOOKUP to find the MAX value of the
list. It works occasionally and only for certain positions in the table. I
wish I could attach my sheet but I think it is easy to understand what I have
done.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

problem with LOOKUP function
 

The function to find the maximum value in a range such as A1:A100 is
=MAX(A1:A100)
What did you use?
By the way, this is a peer-to-peer site. Microsoft employees do not read
these messages
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Boston_north" wrote in message
...
There is a problem with the Excel LOOKUP function that I would like to
have
fixed. It doesn't appear to work with real numbers correctly. I set up a
column of fractional numbers and and used LOOKUP to find the MAX value of
the
list. It works occasionally and only for certain positions in the table.
I
wish I could attach my sheet but I think it is easy to understand what I
have
done.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

problem with LOOKUP function
 

Here is the solution. When you are formating a cell for a fraction the value
you are seeing is not the true value that excel has stored in the worksheet.
It is only what you are seeing. for example 1/3 doesn't equal
..333333333333333 because of rounding errors. You need to look upo the value
EXACTLY the same way it is stored in excel down to the last decimal place.
You can either enter the fraction in thelook up using 1/3 (not
..3333333333333) or use the format statement to give the same formating as the
cell.

For one digit
=vllookup(Text(A1,"# ?/?"),B1:B100)

for two digits

=vllookup(Text(A1,"# ??/??"),B1:B100)

for halves

=vllookup(Text(A1,"# ?/2"),B1:B100)

for quarters
=vllookup(Text(A1,"# ?/4"),B1:B100)

for sixtenths
=vllookup(Text(A1,"# ??/16"),B1:B100)


"Boston_north" wrote:

There is a problem with the Excel LOOKUP function that I would like to have
fixed. It doesn't appear to work with real numbers correctly. I set up a
column of fractional numbers and and used LOOKUP to find the MAX value of the
list. It works occasionally and only for certain positions in the table. I
wish I could attach my sheet but I think it is easy to understand what I have
done.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc