Unable to lookup a time value

iclancy

New Member
Joined
Mar 12, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I am having trouble looking up time values using VLOOKUP. I am attempting to use this formula to look up from the following sheet named "Master Timetable," and return the adjacent value from Column A as a result:
=(VLOOKUP(ROUND($C$6,5),'Master Timetable'!A1:B15,1))

1626889535022.png


The value C6 in the equation is a predetermined time value that matches one of the values in column B. For example, let's use "5:40 PM." My formula rounds Excel's time value to 5 decimal values (which in this case is 0.73611 in General format), since if I don't round it then the two time values end up not matching and the look up fails. The values in the above table are all rounded to 5 decimal places as well, so the value found in column B8 above also shows up as 0.73611 in General format. So, I'm expecting Excel to be able to find this number using VLOOKUP, but it does not, and returns a "N/A" message or "Value Not Available Error."

Please let me know if I have not provided enough information. Thanks!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
Vlookup looks up the value in the 1st column, try
Excel Formula:
=INDEX('Master Timetable'!A1:A15,MATCH(ROUND($C$6,5),'Master Timetable'!B1:B15,0))
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Vlookup looks in the first column of a range for a match. But afai understand the values you want to match are in the second one B trying to return the value in the first column. so this setup cannot work.
Like Fluff just posted you need index + match
 

iclancy

New Member
Joined
Mar 12, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Thanks y'all! Looks like I should have just been using Index instead. Thanks for the help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,148,157
Messages
5,745,107
Members
423,924
Latest member
Gazzat

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
Top