vlookup gives wrong result

[hstaubyn]

Hi,

I am using a vlookup formula to (attempt to) return a date from a table. The columns are arranged as so:

      T           U           V           W           X
1    JobNo      x            y            z           date
2
3    1199       ..           ..           ..          10 Mar 09
4    1188       ..           ..           ..          26 Feb 09
 
etc.

The formula I use is like this:

=vlookup(A6,$T$3:$X$4,5,FALSE)

the table in which this formula resides has job numbers in the 'A' column (same format etc. as lookup table).

The problem is that vlookup returns answers for the date that are completely wrong. For example, instead of returning 15/11/2010 on one of them, it returns 16/03/1900. The two date formats are the same as are the two job number formats (numbers, not text). I am an experienced user of the vlookup function and have never seen this happen before.

Does anyone have an idea of what's going on?

Thanks,

H

 

[baitmaster]

I'm suspicious of anything returning the year 1900. It looks like a low number that is masquerading as a date simply because of the format

Convert the format of that value to a number, is it relevant in some way?

 

[Gerald Higgins]

Is it possible that the job number appears twice in the list, once with a date, and once without, and the vlookup is finding the one without ?

 

[hstaubyn]

Thanks for those ideas.

the job number only appears once in the table and the formatting makes no difference. I tried formatting both the date from the lookup table and the cell containing the formula as numbers, with no success. The number should be 40497, but the formula gives 66 instead. Bizarre!

When I simply type in

=X3

it gives the correct value of 40497 which is why I think the problem lies with vlookup.

It may be worth mentioning that this workbook is pretty big. It contains a large number of array formulae, pivot tables, graphs and also one or two UDFs. Not sure if this should make a difference - I have calculate on manual because of the size. Is it possible for excel to get its maths wrong??

 

[Gerald Higgins]

Is your Vlookup really as simple as in your first post, or is it a bit more complicated ?

Could merged cells be an issue - maybe the date looks like it's in Col X, but it's actually in Col W, that kind of thing ?

 

[baitmaster]

Is it possible for excel to get its maths wrong??

possibly in very rare cases, due to unusual glitches (floating point error etc), but not in this case, and not to this degree

I assume you dont recognise the number 66 in any way? You say the formula is "like" this, is it actually this, or something different? You could also try using INDEX(MATCH instead of using VLOOKUP, its often more robust, and you can test the individual components of it if you get an error

 

[hstaubyn]

=IF($P15="N/K",DATE(N15,VLOOKUP(J15,$AZ$5:$BB$8,3,FALSE),1),VLOOKUP($A15,'Company Data 3'!$AJ$4:$AQ$192,5,FALSE))

That is the actual formula - the only real differences being that the vlookup (the second one) is inside an IF statement and that the lookup table is on a different sheet. Incidentally, P15 is not equal to "N/K".

There are no merged cells in the way. I will try the index approach - thanks for that - but I do still wonder why the result is wrong in this formula.

 

[hstaubyn]

Solved(ish): vlookup gives wrong result

Ok, the index & match method works perfectly, thank you! This has been hurting my brain for a while. The formula below is now giving the correct result:

=IF($P15="N/K",DATE(YEAR(L15),MONTH(L15)+O15,0),INDEX('Company Data 3'!$AQ$4:$AQ$192,MATCH(A15,'Company Data 3'!$AJ$4:$AJ$192,0)))

If anyone knows why vlookup returns the wrong answer I would be very interested to hear your thoughts though...