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.
Aladin
===========
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.
Aladin
============
Posted by Aladin on July 20, 2001 6:53 AM
Aladin,
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
Aladin!
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.
Ian
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.
Aladin
