Selecting Columns in Match Function

sstikons

I'm using the INDEX and MATCH functions. Can someone tell me how to select column headings for the Match Column function? When I try to reference the column headings row, the entire column gets selected. In Excel, MATCH only works by searching a single vector (row or column). Does MATCH in Smartsheet work differently?

Robert S.

Hello,

 

Thanks for the question. The MATCH function in Smartsheet is used in the same way as the one in Excel. Here's our Help Center article with more information on this function (https://help.smartsheet.com/function/match).

 

There's currently no way to reference a column header in Smartsheet. If you'd like to provide more information on the use case for this feature, please submit a Product Enhancement Request using the link in the Quick links section to the right.

 

To create this type of formula, you'll start with the INDEX function which has a syntax as follows:

 

=INDEX(range, row_index, [column_index])

 

The square brackets [ ] that surround the column_index portion of the function denote that this is optional. This is optional is because if the range portion of the function is looking at a range of cells that's only 1 column wide, there's no need to specify which column to pull from. The MATCH function can be used in place of either the row_index, column_index, or both. If your range is only one column wide and you just need to match which row to index, your formula would look something like this:

 

=INDEX([Column 1], MATCH("lookup value", [Column 2]:[Column2]))

 

This will look in the column named "Column 2" for the value "lookup value", and return whatever's in the same row from the column named "Column 1". If you're instead looking to return a value from a range of cells that are multiple columns wide, your formula would look something more like this:

 

=INDEX([Column 2]1:[Column 4]10, MATCH("lookup value", [Column 1]1:[Column 1]10), 2)

 

This INDEX function uses the range [Column 2]1:[Column 4]10, which is 3 columns wide and 10 rows tall. The MATCH function is used to search rows 1 through 10 in the column named "Column 1", the find the value "lookup value". Then the INDEX is set to return column 2 of it's range (which would be the column named "Column 3").

 

If you wanted to have the column selection also be dynamic, you would replace the 2 at the end of this formula with another MATCH function. However to make this work, you would need another row above or below the table of data for the MATCH function to look up values in since it can't look at the column headers.

 

This is a hard thing to fully explain, I hope this helps. Please let me know if you have any further questions on this and I'll be happy to help.

sstikons

Robert, very helpful. Thank you.

I have another question: For the formula below, when I drag this formula that references another grid across columns, the column referenced in the formula doesn't change; it just remains anchored on the column against which I wrote the original formula, in this case {Total Cash Actuals-Forecast Range 2}. Why is this happening? Also, why instead of inserting cell references (e.g. [Column3]2:[Column3]100) is my formula being populated with a range reference? I think your answer to this question may also answer my first question :-)

SUMIF({Total Cash Actuals-Forecast Range 1}, $[Column2]3, {Total Cash Actuals-Forecast Range 2})

Robert S.

Hello,

 

Glad this helped. Ranges of data from another sheet are denoted by the reference's name surrounded by curly braces { }. The default name for a reference is the sheet's name followed by Range and then a number starting at 1 and going up for each reference from that sheet with the same name. In this formula, this is shown as {Total Cash Actuals-Forecast Range 1} and {Total Cash Actuals-Forecast Range 2} because the sheet that's being referenced is named Total Cash Actuals-Forecast and these are the first and second references that have been created with that name. If you're referencing a range of cells on the same sheet as the formula, this will show as [Column Name]row#:[Column Name]row#. So for example, the reference [Start Date]1:[Start Date]10 is referencing the cells in the column named "Start Date" for rows 1 through 10. This is why you're seeing a difference in how the references show.

 

To answer your other question, references to data on another sheet will not automatically change what range is referenced when dragging across columns. If you want to change what column is being referenced in these new columns, you will want to remove the current reference name entirely and create a new one. More on referencing data in another sheet can be found here if needed (https://help.smartsheet.com/articles/2476606).

sstikons

Robert, again, very helpful.

What I need then is the syntax for writing a cell reference in another sheet. I already know the cell reference syntax (e.g., [column2]15), but how do I add to that the other sheet's name?

Robert S.

Hello,

 

Unlike referencing data on the same sheet, you need to create a new reference to data in another sheet which will give it a name. When creating a reference to another sheet, you can also give it a custom name instead of the default one. This is all talked about more in depth in the article I previously mentioned (https://help.smartsheet.com/articles/2476606), but here's the basic steps to create a new reference to another sheet using your formula:

1. In the new formula, remove the current reference you're looking to replace
   • This sounds like it would be the one named {Total Cash Actuals-Forecast Range 2}
2. With your cursor still in that range portion of the formula, click the "Reference Another Sheet" link in the help card
3. Select the new range you'd like to use, and you can give it a name toward the top if you'd like
   • If you don't set a custom name, this will default to the naming scheme I mentioned before
4. Click the "Insert Reference" button at the bottom

This will create the new reference and put it where your cursor was in the formula. Now that the reference is created, you can create other formulas using this reference if you'd like by either doing the same process of selecting the range or if you remember the name you gave the reference you can just type it in to the new formula including the curly braces { }.

sstikons

Robert,

I did as you instructed and was able to change the new reference name. However, when I tried to drag that formula across the columns in my sheet to copy it, the reference didn't change columns. So this solution didn't solve my issue. Remember, what I need to do is copy the formula across columns and have the reference also change columns in the reference sheet, just as you would in any excel formula where the reference cells aren't anchored. Setting my own reference name doesn't accomplish that. Is there something else I need to do to copy across my columns and have the reference column change with the copy?

Robert S.

Hello,

 

As I previously mentioned, references to data on another sheet will not automatically change what range is referenced when dragging across columns and would need to be manually done. You can drag the formula across columns to keep the basic structure of the formula, but you will need to create the new references for each of the new formulas that need them. This is regardless of if the reference is using the default name or a custom name. Apologies if I wasn't clear about this before.

 

If you're able to provide more information on the use case for this, I'd recommend submitting a Product Enhancement Request using the link in the Quick Links section to the right. This will let our Product team know that you're looking for this capability.

sstikons

Hi. Apologies for the misunderstanding.

I will submit a Product Enhancement request.

 

Thank you

michael tomlinson

This is by far the best write up I've seen on this so far, thank you so much.

I know I'm very late to the game on this, but is there any way to make it so that instead of declaring the fields to be used for each row that we could dynamically generate them? 

What I'm trying to do is make it so that I could use a formula to search the data above the new row (pulled in from a form) and pull in any matching data.  I can't use the entire column as it creates a circular reference on the second row and I'm struggling to understand a better way to handle it. 

I currently have:

=INDEX([Primary Column]:[Primary Column], MATCH([Parent UID]@row, [Parent UID]:[Parent UID]))

This works for what I need, but if I create another row with that formula it becomes circular, which I understand why, I'm just not sure how to get around that.