Trouble with VLOOKUP syntax error in some, but not all, column formula selections


I am having some inconsistencies with turning VLOOKUP function into a column formula within the same sheet, I am using VLOOKUP in three different columns. One of the columns allows me to turn it into a "column formula", but the other two give me a syntax error (SEE last and final photo for error message).

See screen shots and detailed explanation below:

I am pulling in VLOOKUP data for the three columns with the blue triangle tags. You will notice that one is a "column formula" and the others are not. This is part of my problem....

You can see my VLOOKUP function here

Pullling from the columns in this other sheet. For some reason the 2nd column will allow the formula to be column based, the the next photo has the same logic and won't allow column formula. (See next photo)

See the same formula, but it is referencing the 3rd column in the reference sheet rather than the 2nd. (Note: this error also happens with the 4th column)

You can see that I am selecting "convert to column formula"

And then I get this error message. When I click on the link, it takes me to something generic that does not address the issue to this level of detail.

Is there a fundamental reason why this happens? or is it just glitchy?

Thanks so much for puzzling this out with me!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!