| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
VLOOKUP Returns #REF
Hi Folks - I am comfortable using the VLOOKUP function. Here's my problem:
I have a 15 row column of TEXT in SHEET1 that looks like: TimeSlot 2-3:30 3-4:30 3-4 etc. The text represents timeslots. So, I added another column and manually entered the minutes for each timeslot. TimeSlot Minutes 2-3:30 90 3-4:30 90 3-4 60 etc. On SHEET2, I have 10,000 rows of class and timeslot information: Session TimeSlot Class1 2-3:30 Class2 3-4:30 etc. I need to lookup the minutes for each session, so, in SHEET2, I used the vlookup function to look at the timeslot in the TimeSlot column, then lookup the slot in SHEET2, to return the minutes in the second column. I get a #REF error. Quick question .. Can Vlookup 'lookup' text? Any ideas? Thanks. Michael |
|
#2
|
|||
|
|||
|
I think this is what you need
=INDEX(Sheet2!A:A,MATCH(A2,Sheet2!B:B,0)) BTW, you can calcualte the minutes with =TIMEVALUE(RIGHT(A2,LEN(A2)-FIND("-",A2)))*1440-LEFT(A2,FIND("-",A2)-1)*60 -- HTH Bob Phillips "Michael" wrote in message news:K7wXe.24987$8q.757@lakeread01... Hi Folks - I am comfortable using the VLOOKUP function. Here's my problem: I have a 15 row column of TEXT in SHEET1 that looks like: TimeSlot 2-3:30 3-4:30 3-4 etc. The text represents timeslots. So, I added another column and manually entered the minutes for each timeslot. TimeSlot Minutes 2-3:30 90 3-4:30 90 3-4 60 etc. On SHEET2, I have 10,000 rows of class and timeslot information: Session TimeSlot Class1 2-3:30 Class2 3-4:30 etc. I need to lookup the minutes for each session, so, in SHEET2, I used the vlookup function to look at the timeslot in the TimeSlot column, then lookup the slot in SHEET2, to return the minutes in the second column. I get a #REF error. Quick question .. Can Vlookup 'lookup' text? Any ideas? Thanks. Michael |
|
#3
|
|||
|
|||
|
Bob - Thanks for the additional help .... but it turns out it was USER ERROR
..... MINE! Michael "Bob Phillips" wrote in message ... I think this is what you need =INDEX(Sheet2!A:A,MATCH(A2,Sheet2!B:B,0)) BTW, you can calcualte the minutes with =TIMEVALUE(RIGHT(A2,LEN(A2)-FIND("-",A2)))*1440-LEFT(A2,FIND("-",A2)-1)*60 -- HTH Bob Phillips "Michael" wrote in message news:K7wXe.24987$8q.757@lakeread01... Hi Folks - I am comfortable using the VLOOKUP function. Here's my problem: I have a 15 row column of TEXT in SHEET1 that looks like: TimeSlot 2-3:30 3-4:30 3-4 etc. The text represents timeslots. So, I added another column and manually entered the minutes for each timeslot. TimeSlot Minutes 2-3:30 90 3-4:30 90 3-4 60 etc. On SHEET2, I have 10,000 rows of class and timeslot information: Session TimeSlot Class1 2-3:30 Class2 3-4:30 etc. I need to lookup the minutes for each session, so, in SHEET2, I used the vlookup function to look at the timeslot in the TimeSlot column, then lookup the slot in SHEET2, to return the minutes in the second column. I get a #REF error. Quick question .. Can Vlookup 'lookup' text? Any ideas? Thanks. Michael |
|
#4
|
|||
|
|||
|
We ourselves are the worst types of user, no-one to blame <vbg
Bob "Michael" wrote in message news:sCwXe.24988$8q.21342@lakeread01... Bob - Thanks for the additional help .... but it turns out it was USER ERROR .... MINE! Michael "Bob Phillips" wrote in message ... I think this is what you need =INDEX(Sheet2!A:A,MATCH(A2,Sheet2!B:B,0)) BTW, you can calcualte the minutes with =TIMEVALUE(RIGHT(A2,LEN(A2)-FIND("-",A2)))*1440-LEFT(A2,FIND("-",A2)-1)*60 -- HTH Bob Phillips "Michael" wrote in message news:K7wXe.24987$8q.757@lakeread01... Hi Folks - I am comfortable using the VLOOKUP function. Here's my problem: I have a 15 row column of TEXT in SHEET1 that looks like: TimeSlot 2-3:30 3-4:30 3-4 etc. The text represents timeslots. So, I added another column and manually entered the minutes for each timeslot. TimeSlot Minutes 2-3:30 90 3-4:30 90 3-4 60 etc. On SHEET2, I have 10,000 rows of class and timeslot information: Session TimeSlot Class1 2-3:30 Class2 3-4:30 etc. I need to lookup the minutes for each session, so, in SHEET2, I used the vlookup function to look at the timeslot in the TimeSlot column, then lookup the slot in SHEET2, to return the minutes in the second column. I get a #REF error. Quick question .. Can Vlookup 'lookup' text? Any ideas? Thanks. Michael |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP using a cell calculated with NOW returns Error | Excel Worksheet Functions | |||
| VLOOKUP with duplicate returns | Excel Discussion (Misc queries) | |||
| Vlookup finds a blank, but returns a zero - HELP! | Excel Discussion (Misc queries) | |||
| When VLOOKUP returns a #N/A How can you get it to replace #N/A wi. | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Linear Mode
