Vlookup Help

[UpTheArgyle]

I am haqving some trouble here

I have 2 columns that i am trying to do a lookup for.
If Cell = XXX = "09:00 - 19:00" then i want the result to return the HOURS value which in this case is 10

The shift column is formated at text and contains 2 spaces, in case that has any bearing)

The resut i am getting though seems random with no number less than 8?

Im puzzzled and its late! any help much appreciated


SHIFT HOURS
09:00 - 19:00 10
10:00 - 20:00 10
11:00 - 21:00 10
12:00 - 22:00 10
13:00 - 23:00 10
14:00 - 00:00 10
15:00 - 01:00 10
16:00 - 02:00 10
17:00 - 03:00 10
18:00 - 04:00 10
19:00 - 05:00 10
20:00 - 06:00 10
21:00 - 07:00 10
22:00 - 08:00 10
23:00 - 09:00 10
00:00 - 10:00 10
01:00 - 11:00 10
02:00 - 12:00 10
03:00 - 13:00 10
04:00 - 14:00 10
05:00 - 15:00 10
06:00 - 16:00 10
07:00 - 17:00 10
08:00 - 18:00 10
09:00 - 18:00 9
10:00 - 19:00 9
11:00 - 20:00 9
12:00 - 21:00 9
13:00 - 22:00 9
14:00 - 23:00 9
15:00 - 00:00 9
16:00 - 01:00 9
17:00 - 02:00 9
18:00 - 03:00 9
19:00 - 04:00 9
20:00 - 05:00 9
21:00 - 06:00 9
22:00 - 07:00 9
23:00 - 08:00 9
00:00 - 09:00 9
01:00 - 10:00 9
02:00 - 11:00 9
03:00 - 12:00 9
04:00 - 13:00 9
05:00 - 14:00 9
06:00 - 15:00 9
07:00 - 16:00 9
08:00 - 17:00 9
09:00 - 17:00 8
10:00 - 18:00 8
11:00 - 19:00 8
12:00 - 20:00 8
13:00 - 21:00 8
14:00 - 22:00 8
15:00 - 23:00 8
16:00 - 00:00 8
17:00 - 01:00 8
18:00 - 02:00 8
19:00 - 03:00 8
20:00 - 04:00 8
21:00 - 05:00 8
22:00 - 06:00 8
23:00 - 07:00 8
00:00 - 08:00 8
01:00 - 09:00 8
02:00 - 10:00 8
03:00 - 11:00 8
04:00 - 12:00 8
05:00 - 13:00 8
06:00 - 14:00 8
07:00 - 15:00 8
08:00 - 16:00 8
09:00 - 16:00 7
10:00 - 17:00 7
11:00 - 18:00 7
12:00 - 19:00 7
13:00 - 20:00 7
14:00 - 21:00 7
15:00 - 22:00 7
16:00 - 23:00 7
17:00 - 00:00 7
18:00 - 01:00 7
19:00 - 02:00 7
20:00 - 03:00 7
21:00 - 04:00 7
22:00 - 05:00 7
23:00 - 06:00 7
00:00 - 07:00 7
01:00 - 08:00 7
02:00 - 09:00 7
03:00 - 10:00 7
04:00 - 11:00 7
05:00 - 12:00 7
06:00 - 13:00 7
07:00 - 14:00 7
08:00 - 15:00 7
09:00 - 15:00 6
10:00 - 16:00 6
11:00 - 17:00 6
12:00 - 18:00 6
13:00 - 19:00 6
14:00 - 20:00 6
15:00 - 21:00 6
16:00 - 22:00 6
17:00 - 23:00 6
18:00 - 00:00 6
19:00 - 01:00 6
20:00 - 02:00 6
21:00 - 03:00 6
22:00 - 04:00 6
23:00 - 05:00 6
00:00 - 06:00 6
01:00 - 07:00 6
02:00 - 08:00 6
03:00 - 09:00 6
04:00 - 10:00 6
05:00 - 11:00 6
06:00 - 12:00 6
07:00 - 13:00 6
08:00 - 14:00 6
09:00 - 14:00 5
10:00 - 15:00 5
11:00 - 16:00 5
12:00 - 17:00 5
13:00 - 18:00 5
14:00 - 19:00 5
15:00 - 20:00 5
16:00 - 21:00 5
17:00 - 22:00 5
18:00 - 23:00 5
19:00 - 00:00 5
20:00 - 01:00 5
21:00 - 02:00 5
22:00 - 03:00 5
23:00 - 04:00 5
00:00 - 05:00 5
01:00 - 06:00 5
02:00 - 07:00 5
03:00 - 08:00 5
04:00 - 09:00 5
05:00 - 10:00 5
06:00 - 11:00 5
07:00 - 12:00 5
08:00 - 13:00 5
09:00 - 13:00 4
10:00 - 14:00 4
11:00 - 15:00 4
12:00 - 16:00 4
13:00 - 17:00 4
14:00 - 18:00 4
15:00 - 19:00 4
16:00 - 20:00 4
17:00 - 21:00 4
18:00 - 22:00 4
19:00 - 23:00 4
20:00 - 00:00 4
21:00 - 01:00 4
22:00 - 02:00 4
23:00 - 03:00 4
00:00 - 04:00 4
01:00 - 05:00 4
02:00 - 06:00 4
03:00 - 07:00 4
04:00 - 08:00 4
05:00 - 09:00 4
06:00 - 10:00 4
07:00 - 11:00 4
08:00 - 12:00 4

 

[dafan]

Where do you input your search-arguments, ie 09:00 and 19:00 ?
And what does your current formula look like?

 

[whiskycan]

Assuming your times are in column A and your hours are in column B, the formula you need is:

=VLOOKUP("09:00 - 19:00",A:B,2,0)

The False is important as this will force an exact match.

 

[UpTheArgyle]

Thank you, that worked like a dream


Any idea if i could return a value of Zero if the cell in empty?

 

[whiskycan]

Try this...

=IF(ISNA(VLOOKUP("09:00 - 19:00",A:B,2,0)),0,VLOOKUP("09:00 - 19:00",A:B,2,0))

You might also want to replace both occurrences of "09:00 - 19:00" in the formula to a cell reference, say D1. Then select cell D1 and choose Data > Validation, set the Validation to List and set the list reference to A:A. This way you can select any of the shifts in your list from a drop-down menu.