VLOOKUP Problem

JackieSS

New Member
Joined
Apr 14, 2020
Messages
2
Office Version
2016
Platform
Windows
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,497
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,808
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
=VLOOKUP(round(H7,0),C47:F62,4,FALSE)
 

JackieSS

New Member
Joined
Apr 14, 2020
Messages
2
Office Version
2016
Platform
Windows
Thank you sooo much! I've messed with this for ages and both of these options worked. Glad I became a member :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,808
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,281
Messages
5,467,733
Members
406,549
Latest member
midcoastchris04

This Week's Hot Topics

Top