|
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 06:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com