VLOOKUP Problem

[JackieSS]

I have a cell using the formula =(DATEa-DATEb)/365 ( actual formula is =(C11-E7*1/365*1 where cells C11 and E7 are dates) to give me the number of years an employee has worked at the company. The cell is formatted to a number with zero decimal places so that it will round up to whole numbers. The cell address is H7

I am using this cell in a VLOOKUP formula =VLOOKUP(H7,C47:F62,4,FALSE)

It returns #N/A

I know it is something to do with the result of the formula in cell H7 because if I see what the result is, say 5, and type 5 into H7 instead of the formula then the VLOOKUP works.

I have all the data in the table formatted to be a number and I have tried forcing the result in H7 to be a number by +0 or *1

Thanks

 

[svendiamond]

Instead of rounding it to 5, force it to be an integer by using =INT(H7)

so VLOOKUP( INT(H7), C47:F62, 4, FALSE)

 

[Fluff]

Hi & welcome to MrExcel.
How about
=VLOOKUP(round(H7,0),C47:F62,4,FALSE)

 

[JackieSS]

Thank you sooo much! I've messed with this for ages and both of these options worked. Glad I became a member

 

[Fluff]

Glad we could help & thanks for the feedback