MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Lookup function

Posted by Mike Wortman on July 20, 2001 4:41 AM

I'm trying to accomplish a very simple task and don't know the appropriate function to use. Here's what I want to do:

I want Excel to recognize a "work shift" on a schedule and return a value that represents the # of hours worked on that shift.
For example: the text string "7-6" would return a value of 11 (for an 11hr shift), "7-11" returns a value of 4, "12-8" a value of 7.5, etc.
I can accomplish it using IF,THEN statements but the formulas become very long because I have a lot of different shifts - so I thought a "lookup" table might be a more elegant solution. I have no experience with this funtion and cannot get it to consistently return the correct value. Maybe I should use MATCH or INDEX?
Here's the table I made:
shift hours
"7-11" 4
"7-3" 7.5
"6-2" 7.5
"11-7" 7.5
"12-8" 7.5
"7-6" 11
"4-8" 4
I want a formula that returns the value in th right column from the text string in the left column.


Posted by Ian on July 20, 2001 4:58 AM

Do they need to be text strings??

Hi, would not the easiest answer be to have 2 columns IN and OUT in time format them column C: to be =b:-a:

If you want you can e.mail me an e.g.


Posted by Aladin Akyurek on July 20, 2001 5:02 AM


Assuming that the table is in the range A2:B8, the following should what you want:


where E1 contains the lookup value, that is, the shift you're interested in.



Posted by Aladin Akyurek on July 20, 2001 5:04 AM

Re: Do they need to be text strings??


I don't think that should matter.



Posted by Aladin on July 20, 2001 6:53 AM

That worked perfectly, exactly what I wanted to do. You're a prince (or princess)! Whatever.

Posted by Ian on July 22, 2001 2:39 PM


As i explained in my reply, it would be easier to calculate time values than test each one for conditions based on text.
And could "7-11" used in the original question not also be a 16hr shift.


Posted by Aladin Akyurek on July 22, 2001 10:51 PM

Data Issue

Ian -- I took your reply as saying that VLOOKUP would not work with such text values, an unfortunate conclusion.