bizarre and annoying vlookup problem, help needed!

[plang]

Hi,

I am getting a weird problem with vlookup. Certain numbers don't seem to work when they are the result of a formula and are contained in a cell reference that's being looked up. For example in the formula

=vlookup(a1,b1:c100,2,false)

if a1=7 and is the result of another formula, then the answer to the vlookup is #n/a. However if I manually type in 7 to A1, or copy and paste special 'Values', the formula works! Similarly if I change the formula that feeds into A1 so that A1 reads 6 instead, the formula also works.

You can test this out by copying and pasting this into cells A1:F2 (and doing text to columns if necessary to remove the spaces)

1 100 =a1/b1*100 =vlookup(c1,$e:$f,2,false) 1 =rand()
=a1+1 =b1 =a2/b2*100 =vlookup(c2,$e:$f,2,false) =a1+1 =rand()

If you then select row 2 and drag it down for a few hundred rows, some of the vlookups in column D don't work (they don't work for me anyway...), for example 7, 14, 28, 29, 55, 56, 57, 58....

If I change the 'false' in the lookup to 'true' then everything does work, but I'm just curious as to why this should happen in the first place? Surely I can't be the only person that this happens to?!

Please help...
cheers
Pete

[oldchippy]

Hi,

I am getting a weird problem with vlookup. Certain numbers don't seem to work when they are the result of a formula and are contained in a cell reference that's being looked up. For example in the formula

=vlookup(a1,b1:c100,2,false)

if a1=7 and is the result of another formula, then the answer to the vlookup is #n/a. However if I manually type in 7 to A1, or copy and paste special 'Values', the formula works! Similarly if I change the formula that feeds into A1 so that A1 reads 6 instead, the formula also works.

You can test this out by copying and pasting this into cells A1:F2 (and doing text to columns if necessary to remove the spaces)

1 100 =a1/b1*100 =vlookup(c1,$e:$f,2,false) 1 =rand()
=a1+1 =b1 =a2/b2*100 =vlookup(c2,$e:$f,2,false) =a1+1 =rand()

If you then select row 2 and drag it down for a few hundred rows, some of the vlookups in column D don't work (they don't work for me anyway...), for example 7, 14, 28, 29, 55, 56, 57, 58....

If I change the 'false' in the lookup to 'true' then everything does work, but I'm just curious as to why this should happen in the first place? Surely I can't be the only person that this happens to?!

Please help...
cheers
Pete

Hi Pete,

When you say if a1=7 and is the result of another formula, the chances are this is something like 7.1234567 and not seven

[plang]

Hi,

Thanks, but that's not the answer sadly - when I do copy/paste special/values on the cell, the formula works, so they are definitely whole numbers. I've tested this as well by putting 7 in a different cell (say G1) and then doing a formula along the lines of =if(a1=g1,"Y","N"), and it comes up as "Y". So the number in the cell is definitely exactly 7, no hidden decimal points, but it's still not found in the lookup....

cheers
Pete

[Bryan Hessey]

Hi,

Thanks, but that's not the answer sadly - when I do copy/paste special/values on the cell, the formula works, so they are definitely whole numbers. I've tested this as well by putting 7 in a different cell (say G1) and then doing a formula along the lines of =if(a1=g1,"Y","N"), and it comes up as "Y". So the number in the cell is definitely exactly 7, no hidden decimal points, but it's still not found in the lookup....

cheers
Pete

It would appear to be a problem generated in the C column. If you lookup(int(c~), you achieve a succesful lookup.

---
the old '10 cents' rule ?