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

Sarah_lee123

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!

Genevieve P.

Hi @Sarah_lee123

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

Sarah_lee123

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

Genevieve P.

No problem!

Sarah_lee123

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!

Sarah_lee123

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.

Genevieve P.

Hi @Sarah_lee123

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