Exact lookup questions

[dougaj4]

These questions are more out of curiosity than anything else. I have
been experimenting with VLOOKUP with an exact match, and would like
some background to what seems to me to be curious behaviour.

In cells A1:A4 I have

0.1
0.5
=1/3
0.4

The =1/3 displays as 0.333333333 in the cell and as =1/3 in the
formula bar.

In C1 I have: =VLOOKUP(D1,A1:A4,1,FALSE)

In E1 I have =D1-A3, formatted as scientific with 2 decimal places.

If I enter =1/3 in cell D1 I get a match and E1 displays 0.00E+00, as
expected.

Now for the curious bits.

If I enter 0.333333333333333 in D1 (15 3's) then E1 knows this is as
close to 1/3 as you can get with the available precision, and displays
0.00E+00, but VLOOKUP doesn't seem to know this and displays #N/A.

If I enter +1/3 this displays as 0.333333333333333 in the formula bar,
but I get a match.

If I now press F2-enter the display in the formula bar doesn't change,
but the VLOOKUP gives #N/A. E1 still displays 0.00E+00.

All this is with XL2007.

My questions are:

Why does entering +1/3 convert itself into 0.333333333333333 in the
formula bar, but =1/3 doesn't?

Why does the value used in the VLOOKUP continue to be 1/3 until I
press F2-enter?

If subtracting two very close (but not exactly equal) numbers results
in a display of exactly zero, why doesn't VLOOKUP work the same way?

Is there a lookup or match function that allows me to specify a
precision for an exact match, or do I have to write my own?

 

[Guest]

Doug

Type A to d in column B, change the formula =1/3 to =ROUND(1/3,15).
Change the lookup formula to =VLOOKUP(D1,A1:B4,2,0)
Try sorting the list by value and then by column b.

 

[dougaj4]

Doug

Type A to d in column B, change the formula =1/3 to =ROUND(1/3,15).
Change the lookup formula to =VLOOKUP(D1,A1:B4,2,0)
Try sorting the list by value and then by column b.

Billy - thanks for the response, but I'm really interested in the
reasons for the behaviour with an exact match on an unsorted list.

Answering my own last question, the formula below, entered as an array
formula, will give an exact match to 10 decimal places (or whatever
you want):

=VLOOKUP(ROUND(D1,10),ROUND(A1:A4,10),1,FALSE)