# 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.

Help!

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.

Ian

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

Mike,

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

=VLOOKUP(E1,\$A\$2:\$B\$8,2,0)

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??

Ian,

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.
Thanx.

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