Go Back  DVD Talk Forum > General Discussions > Tech Talk
Reload this Page >

Excel? - Matching an absolute value to a range ..

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Excel? - Matching an absolute value to a range ..

Old 01-31-08, 01:13 PM
DVD Talk Gold Edition
Thread Starter
visitor Q's Avatar
Join Date: Oct 2004
Posts: 2,501
Excel? - Matching an absolute value to a range ..

Hey all. I am in search of a solution and hope that someone may be kind enough to offer a little headway.

Data Set:

A - B - C
1 - Store # - Time - Inventory
2 - 139 - 17:15 - 950
3 - 139 - 18:15 - 870
4 - 139 - 19:15 - 850
5 - 139 - 20:15 - 850


E - F
Store # - Cut off Time
067 - 18:00
139 - 18:25
140 - 18:00
151 - 19:00

Basically what I am trying to do is out of a long data set of multiple stores and times get the inventory result for the closest time (only) given to the absolute cut off time listed within the table. -or- in this example, since the cut off time for 139 is 18:25 PM then my best inventory result for the closest time is 870.

Each "closest time" from the data set are time stamps from readings taken for a liquid product every hour.

=IF(VLOOKUP(A2,$E$2:$F$5,6,FALSE)=B2*,C2,0) is the formula I would consider for this excercise. However since the time value in each row within the data set will most likely never exactly match the time on the table - this formula does not work in its current form.

I've tried adding an arithmatic operators such "+-" or "+/-" where the asterick is situated in the formula but its not working.

Thank you in advance!
visitor Q is offline  
Old 01-31-08, 02:53 PM
Senior Member
Join Date: Jun 2001
Location: dc-md-va
Posts: 959
Have you tried using True instead of False in your Vlookup? True is supposed to return a nearest value. I've never used it myself but might work in this case. Or, alternatively, maybe parse the hour out with HOUR() and try to get an exact match, within the hour?
dleedlee is offline  

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off

Thread Tools
Search this Thread

Archive Advertising Cookie Policy Privacy Statement Terms of Service

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.