Trying to assign a number value to non-numerical value but still display the text not number

Occasional Contributor

I am working on a timesheet and I need the cells to show the text like "Morning, Evening, Night" etc but the cell needs to have a numerical value but still show the text, not the value. The numerical value then gets pulled onto another sheet by the date which I I did this with


Here is the first sheet that needs to display the text not the numerical value.  Like we would want it so that if say "Morning" was typed in, 8 hours would go to the other sheet using the VLOOKUP in cell : 




This is the second sheet that the numerical value gets pulled to using the VLOOKUP by the date: 





Is this possible? thanks for the help!

10 Replies


It's not possible. Use one column for the numbers and another column for the text values.

great thanks for that. I told my coworker this but he said there must be a way....



Should the user enter 8 or Morning ?



They want the user to enter the non-numerical text like "Morning, Evening, Night" and then it will show that on the main page, but when the VLOOKUP is used on the second tab, it puts the corresponding number like 8, 12





The blue function find "Morning" or "Evening" or "Night"...

The red convert "Morning" in 8 for exemple


I may not have understood


how am I able to use the VLOOKUP with columns that are not beside each other?


I have this working because the columns are beside each other, 


but I also need it to work for column D, E and F by the date.  There are other columns between the date column (A) and the other ones (D, E and F)