Do we have a way to reference column names yet?

CycleBagEd

I'd like to be able to write robust lookups which don't rely on a fixed column reference to return from a cross-sheet link.

For example, I'd like to do a lookup on a row value (@row) in "ID Number" and bring back the value from another sheet e.g. "Project Name" where the ID number matches BUT specify that I want to retrieve the value from the column with the same name as the column I am retrieving the value in.

We do this in Excel using index and match, of course the header fields are typically in row 1 and so can be matched to.

I realise I can recreate the field names in row one and possibly even hide that row, but it's a bit of a workaround and would rule out column formulas.

Any advice welcome, thanks in advance.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/315930#Comment_315930

Smartsheet's INDEX/MATCH references the column in the other sheet, no matter where it moves to or even if it's renamed. This is because there's an underlying Column ID that the code is referencing behind the scenes. So a column at the left called "Name" could be moved to the far right of the sheet and be renamed "George," and your formula wouldn't break.

When you start to build your INDEX/MATCH, when you get to here

=INDEX(

Smartsheet will popup a helper screen and there's a link to Reference Another Sheet. Select the sheet and the column, name the range whatever you want, and you're all set.

CycleBagEd

Thanks Jeff,

It's very useful to know that it is actually referencing the ID.

CycleBagEd

It's clearly getting late - the answer is here, and I had my sdrawkcab xatnys.

https://community.smartsheet.com/discussion/69412/how-do-i-use-the-index-match-function-when-trying-to-pull-information-from-one-sheet-into-another

Doh!

Jeff Reisman

Can you describe a little more what you're trying to achieve?

Smartsheet's INDEX/MATCH lets you specify where you want to retrieve results from, so I'm not sure what kind of workaround you are looking for. A screenshot of some sample data and sheets might help.

CycleBagEd

Thanks Jeff.

I'd like to be able to write a formula which doesn't break if you re-arrange the columns on the sheet which is being referenced for the lookup, i.e. where the data is being retrieved from.

Sheet 1:

[ID] [Full Name] [Other Field]

Sheet 2:

[ID] [Full Name]

On sheet 2, there is no data in [Full Name]. I need to retrieve it from Sheet A. However, I would like to refer to the column name instead of the column_num because column_num is fixed and if I re-order sheet 1 so that [Other Field] is in column 2, the formula will break. These things happen, especially while you are in the building phase.

In Excel, you can use the field headers to do this - they are in row one. INDEX and MATCH do the job.

To do this in smartsheets, I'd need to be able to refer to the column name in the formula.

The only workarounds I have for this right now is using a fixed text reference "Full Name" and then referencing a range on the lookup sheet which repeats the row headers in Row 1 on what would be sheet 1 in the example above. This brings back the correct column_num dynamically. But, it's a hack.

Another hack is to use a third sheet which I have to keep updated with the column orders from Sheet 1, retrieve the column_num from there and this avoids the need for a repeated set of headers in row one of sheet 2, but the need to carry on with this type of hack is less than ideal.

I don't really like either solution, but both are far better than me adjusting the formulae on 150 project templates because the index sheet I am using to store project data had the columns re-arranged.

Better ideas are welcome!

Andrée Starå

Hi @CycleBagEd

I hope you're well and safe!

To add to Jeff’s excellent advice/answer.

If you use INDEX/MATCH, you can move them around. With VLOOKUP, you can't.

You can also name the reference the same as the column name, so it would be easier to know what it is.

Make sense?

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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/315930#Comment_315930

Smartsheet's INDEX/MATCH references the column in the other sheet, no matter where it moves to or even if it's renamed. This is because there's an underlying Column ID that the code is referencing behind the scenes. So a column at the left called "Name" could be moved to the far right of the sheet and be renamed "George," and your formula wouldn't break.

When you start to build your INDEX/MATCH, when you get to here

=INDEX(

Smartsheet will popup a helper screen and there's a link to Reference Another Sheet. Select the sheet and the column, name the range whatever you want, and you're all set.

CycleBagEd

Thanks Jeff,

It's very useful to know that it is actually referencing the ID.

CycleBagEd

Actually Jeff, I can't get the syntax right on this. Bearing in mind I want to retrieve a value from an adjacent column, I need to reference a range that is more than one column wide.

Let's say I have 3 columns, User, Contract and Rate on sheet 2

I want to retrieve data from Rate based on values in User. User is present on sheet 1 as "Assigned User"

If I use the following: =INDEX({User}, MATCH([Assigned To]@row, {User}, 0)) I retrieve the user from the sheet I am indexing - so, the syntax is good, but this is of no use - I already know the user.

If I want to specify a return column I have to do this as a column_index, in this case "3" and in which case my lookup range/reference has to be defined as multiple columns...and which, if I realise I want to insert a column between "Contract" and "Rate" will totally break my formula.

The only workarounds to this are the ones I mention upthread. If there was a way of saying "look in this column and then bring back data from this other column, but the column might move so I want to reference the column please and not how far away it is from another column" that would be most useful, but it seems this isn't possible without hacking into the first row and writing the column name (may it never change!) into the MATCH formula.

CycleBagEd

It's clearly getting late - the answer is here, and I had my sdrawkcab xatnys.

https://community.smartsheet.com/discussion/69412/how-do-i-use-the-index-match-function-when-trying-to-pull-information-from-one-sheet-into-another

Doh!