VLOOKUP Problem

JackieSS

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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Instead of rounding it to 5, force it to be an integer by using =INT(H7)

so VLOOKUP( INT(H7), C47:F62, 4, FALSE)
 
Upvote 0
Hi & welcome to MrExcel.
How about
=VLOOKUP(round(H7,0),C47:F62,4,FALSE)
 
Upvote 0
Thank you sooo much! I've messed with this for ages and both of these options worked. Glad I became a member :)
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,215
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top