Find value (time) in a range and apply a condition

[Simona]

Hi,
I’m new to the board and I need some help.
I have 3 columns in Excel with the “Train number”, “Route” and “Time of departure” and I need to allocate the trains (4th column) using a list of meals according to the time of departure:
<table class="MsoNormalTable" style="width: 314pt; margin-left: 4.65pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="419"> <tbody><tr style="height: 15pt;"> <td style="width: 107.1pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="143"> Route<o></o>
</td> <td colspan="2" style="width: 114.4pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="153" nowrap="nowrap">

Time Range<o></o>​

</td> <td style="width: 69.85pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="93" nowrap="nowrap"> Meal<o></o>
</td> <td style="width: 22.65pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="30" nowrap="nowrap">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 107.1pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="143"> ALL <o></o>
</td> <td style="width: 49.6pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="66" nowrap="nowrap">

00:00:00<o></o>​

</td> <td style="width: 64.8pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="86" nowrap="nowrap">

09:29:00<o></o>​

</td> <td style="width: 69.85pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="93" nowrap="nowrap"> BKF<o></o>
</td> <td style="width: 22.65pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="30" nowrap="nowrap">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 107.1pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="143"> ALL <o></o>
</td> <td style="width: 49.6pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="66" nowrap="nowrap">

09:30:00<o></o>​

</td> <td style="width: 64.8pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="86" nowrap="nowrap">

10:59:00<o></o>​

</td> <td style="width: 69.85pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="93" nowrap="nowrap"> BKF+<o></o>
</td> <td style="width: 22.65pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="30" nowrap="nowrap">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 107.1pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="143"> ALL<o></o>
</td> <td style="width: 49.6pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="66" nowrap="nowrap">

11:00:00<o></o>​

</td> <td style="width: 64.8pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="86" nowrap="nowrap">

13:59:00<o></o>​

</td> <td style="width: 69.85pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="93" nowrap="nowrap"> DTM<o></o>
</td> <td style="width: 22.65pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="30" nowrap="nowrap">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 107.1pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="143"> LISBON<o></o>
</td> <td style="width: 49.6pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="66" nowrap="nowrap">

14:00:00<o></o>​

</td> <td style="width: 64.8pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="86" nowrap="nowrap">

16:29:00<o></o>​

</td> <td style="width: 69.85pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="93" nowrap="nowrap"> ALM<o></o>
</td> <td style="width: 22.65pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="30" nowrap="nowrap">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 107.1pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="143"> MADRID<o></o>
</td> <td style="width: 49.6pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="66" nowrap="nowrap">

14:00:00<o></o>​

</td> <td style="width: 64.8pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="86" nowrap="nowrap">

17:29:00<o></o>​

</td> <td style="width: 69.85pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="93" nowrap="nowrap"> ALM<o></o>
</td> <td style="width: 22.65pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="30" nowrap="nowrap">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 107.1pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="143"> LISBON<o></o>
</td> <td style="width: 49.6pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="66" nowrap="nowrap">

16:30:00<o></o>​

</td> <td style="width: 64.8pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="86" nowrap="nowrap">

23:59:00<o></o>​

</td> <td style="width: 69.85pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="93" nowrap="nowrap"> DIN<o></o>
</td> <td style="width: 22.65pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="30" nowrap="nowrap">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 107.1pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="143"> MADRID<o></o>
</td> <td style="width: 49.6pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="66" nowrap="nowrap">

17:30:00<o></o>​

</td> <td style="width: 64.8pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="86" nowrap="nowrap">

23:59:00<o></o>​

</td> <td style="width: 69.85pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="93" nowrap="nowrap"> DIN<o></o>
</td> <td style="width: 22.65pt; padding: 0cm 5.4pt; height: 15pt;" valign="bottom" width="30" nowrap="nowrap">
</td> </tr> </tbody></table> <o> </o>
Question 1: which formula should I use in order to return the meal for a train that, for example, departures at 9:54 as I have only the time ranges?
Question 2: solved the problem above, how can I make it work including the route (as you can see on the table the time range for the meals changes according to the route).
Thank you for your help!
NB: Better if not VBA
Simona

 

[BobUmlas]

In your example, if the word "Route" is in A1, and you want to get the info from a time entered in F3, for example:
Q1: =VLOOKUP(F3,B2:D10,3)
Q2: =LOOKUP(F3,B2:B10,A2:A10)

HTH

 

[Simona]

Great!!!
in the Q1 I was always using the criteria "False" at the end of the vlookup formula which returns only exactly matches! Take it off and it will work perfectly with ranges.
For the second question the lookup formula is not enough but I used a nested "if" and now it's working nice!!!
Many thanks!