Trouble with VLOOKUP syntax error in some, but not all, column formula selections
Hello,
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!
Answers

The reason one formula works as a Column Formula and the other formulas don't work is because your first formula uses @row as the row reference for the first cell you're referencing, whereas the other formulas are using the row number  "1"
You'll need to adjust the cell reference to use @row in order for it to be dynamic and apply to each row in the sheet:
=VLOOKUP([Column Name]@row
See: Create Efficient Formulas with @cell and @row and Use column formulas to apply calculations to all rows in a sheet
Cheers,
Genevieve

Ok great! I’m not very well versed in formula creation, but I am fascinated with the possibilities. Thanks for your help!

No problem!

Hu @Genevieve P. , I just tried converting it to a row formula and ended up with the same syntax error. It seems like there might be another issue at play here. Any other ideas?
This one is a row formula, but comes with the same syntax error. It will fill in the first row, and any row that I drag the formula to, but will not allow column formula.
thx!

NEVERMIND! I figured it out. It work fine. I think I either had a different formula in different cells within the column and didn't pay attention. OR it's because I had a $ in the formula to lock a column. Either way, it is working now. Thx again.

I'm glad you figured it out! Yes, it was likely the $ in front of your cell reference. Column formulas can't have $ or direct row numbers.
Cheers!
Genevieve
Help Article Resources
Categories
Check out the Formula Handbook template!