How to reference column name in formula

Natasha Stephens

So I want to use a vlookup that compares a column name to pull information. Obviously if I were referencing a cell, I would type ColumnNameRow#. However, the column name does not have a row number? How do you reference it in formulas?

Paul Newcome

TO reference an entire column, you would use

[Column Name]:[Column Name]

Natasha Stephens

I don't want to reference an entire column, I just want to reference the column name

Andrée Starå

Hi @Natasha Stephens

I hope you're well and safe!

Unfortunately, it's not possible now, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment.

As a possible workaround, you could add a so-called helper row with the column names and reference that.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Exactly why do you just want a column name without a row number?

Natasha Stephens

I don't want a column name without a row number. This is just how smartsheet is set up. Unlike excel, row 1 starts below the column name. Therefore, I cannot reference a column name in a formula.

Paul Newcome

I'm not sure I follow exactly what you are trying to accomplish.

To reference a specific row within a certain column you would use (for row 1 as an example)

[Column Name]1

If your formula is also on row 1 you can use

[Column Name]@row

Genevieve P.

Hiya!

I think what @Natasha Stephens is trying to do is reference the column name as a value in the formula... so for example, to return the name of the column if a matching value is found. Is that correct?

In this instance, @Andrée Starå's suggestion above is what I would say, too. You can create a helper-row at the top of the sheet that has column names duplicated in a row so that you can reference these cells and values.

If that won't work for you, it would be helpful to know a bit more about your process, sheet set-up, and end goal and we'll be happy to help come up with other suggestions!

Cheers,

Genevieve

Paul Newcome

@Genevieve P That makes sense. Ugh. Make a match on a column name for the VLOOKUP to determine the column number. I need a nap. Haha

Natasha Stephens

Yes! Genevieve is right. I wanted to use the column name as a part of my vlookup reference. I can create a helper row but that is obviously duplicate work and wanted to avoid it if there was an easier solution. It seems wild that they would make information untouchable for reference in a formula.

Paul Newcome

@Natasha Stephens There may or may not be another option depending on how your sheets are set up.

Are you trying to dragfill this VLOOKUP so that you can eventually pull multiple columns in from the source sheet? There have been times where I personally have been able to make it work using a COUNT function in place of the column number in the VLOOKUP.

Are you able to provide screenshots of both sheets (at least the column names/order) and describe in more detail the overall goal for the VLOOKUP referencing a column name?

Natasha Stephens

Hello,

I had to create a helper row with the exact same data as the column name. I want the cell to look and see what the title is and pull the appropriate information from the other sheet.

Robert Ford

I would also like the ability to reference column names in formulas. I just submitted this as an enhancement request: "I would like to be able to reference column names in formulas, much like what can be done in Excel with structured references: [#Headers],[column_name]. This would eliminate the need for "helper rows" that add unnecessary clutter to sheets, card views, etc."

Jennifer Schierhout

The helper row does not work if you have Column formulas :( @Genevieve P. @Paul Newcome

I agree with @Robert Ford suggestion - as I would like to use an index match formula to match column/row and return a value from a lookup table (in another sheet)

Paul Newcome

@Jennifer Schierhout Depending on your exact setup and needs, sometimes there is a way to do that. If you want to provide some screenshots, I'd be happy to take a look to see if your particular instance is one where we can.

Robin Herrick

https://community.smartsheet.com/discussion/comment/300939#Comment_300939

Could you offset your helper row to the right?