#INVALID DATA TYPE with a VLOOKUP formula

ba_stone

I need to turn a column of dates into two additional columns, one with the month name and one with the year. The year was easy. But when using the MONTH formula, it returns the month as a number, 1-12. No big deal; as a workaround, I set up a "helper table" in another sheet with the numbers 1-12 and their corresponding month names, thinking I could then add a Month Name column to my original sheet for a VLOOKUP that would match the month number in my Month column to the month number in the helper table, and return the corresponding text value (the month name). 

Here's my formula: 

=VLOOKUP(Month1, {Month Helper Table Range 1}, {Month Helper Table Range 2})

For reasons I cannot figure out, I continue getting the error #INVALID DATA TYPE. 

• I've tried using the VALUE formula in both sheets to ensure neither number column is accidentally being treated as text and inadvertently causing a miscommunication
• I've tried variations in syntax of the original search value: [Month]1, Month1, "Month1"
• I've tried taking out the cell reference altogether as the search value and just trying something static: 6, "6"
• I've tried converting the helper table month numbers to text values instead -- turning 6 to six -- and using a search value of "six"
• My lookup table {Month Helper Table Range 1} is defined using both columns (the month number in the left-most column and the month name in a contiguous column on its right)
• My column number {Month Helper Table Range 2} is defined as just Column2 with the month names as text
• I've checked all my column properties in both sheets; all are set as Text/Number
• I've tried more narrowly defining the column for the return values by selecting just the cells with January through December, as well as selecting the whole column
• I've used true, false, and blank for the match type

I'm really not sure what else to do. It makes zero sense to me why it keeps coming back with #INVALID DATA TYPE, especially given that this is not a particularly complicated use of a VLOOKUP. 

What am I missing??

Andrée Starå

Hi,

Try something like this.

=IF(MONTH(Month@row) = 12; "December"; IF(MONTH(Month@row) = 11; "November"; IF(MONTH(Month@row) = 10; "October"; IF(MONTH(Month@row) = 9; "September"; IF(MONTH(Month@row) = 8; "August"; IF(MONTH(Month@row) = 7; "July"; IF(MONTH(Month@row) = 6; "June"; IF(MONTH(Month@row) = 5; "May"; IF(MONTH(Month@row) = 4; "April"; IF(MONTH(Month@row) = 3; "March"; IF(MONTH(Month@row) = 2; "February"; IF(MONTH(Month@row) = 1; "January"; ""))))))))))))

The same version but with the below changes for your and others convenience.

=IF(MONTH(Month@row) = 12, "December", IF(MONTH(Month@row) = 11, "November", IF(MONTH(Month@row) = 10, "October", IF(MONTH(Month@row) = 9, "September", IF(MONTH(Month@row) = 8, "August", IF(MONTH(Month@row) = 7, "July", IF(MONTH(Month@row) = 6, "June", IF(MONTH(Month@row) = 5, "May", IF(MONTH(Month@row) = 4, "April", IF(MONTH(Month@row) = 3, "March", IF(MONTH(Month@row) = 2, "February", IF(MONTH(Month@row) = 1, "January", ""))))))))))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD