Text recognition with VLOOKUP?

**Peregrine** · 02-06-2010, 06:37 PM

For staffing/ payroll purposes, I am putting together a spreadsheet which will convert the number of hours a person works in a day to set shift times. I have used a VLOOKUP function with success if standard hours are entered.

The problems start when I require the program to identify annual leave and public holidays. While numbers have worked, I have not been able to get the VLOOKUP function to recognise text with accuracy. It always puts something in the cell but the data is not accurate.

Please find attached an example of how I am entering the formula & data I am working with.

Can I use the VLOOKUP function for this purpose or do i need an alternate formula?

With thanks

**teylyn** · 02-06-2010, 06:42 PM

Hi Peregrine,

welcome to the forum.

you are omitting the last argument of the Vlookup, so it defaults to TRUE. Use

=VLOOKUP(C6,($J$4:$K$44),2,FALSE)

and it will return the correct value.

**Richard Buttrey** · 02-06-2010, 06:49 PM

Hi,

The VLOOKUP() is returning exactly what you're asking it to do. Why would you expect for instance 9AL to return '8.30 - 5.00 Paid annual leave' when clearly this does not feature in your table.

However I'd urge you to take a step back and review what you're trying to achieve. If you want to do any sort of date/time arithmetic, and particularly if you are needing to take account of annual leave and public holidays where you will need to incorporate the NETWORKDAYS() function in any solution, then you will need to start with date and time numbers and not strings of text.

HTH