Index and match functions

Debbie Sin

I would like to use Index and match functions.  I have done this only if all the relevant data are on the same sheer.  However, if I need to index and get the value from another sheet, How can I do that?

Thanks

 

Robert S.

Hello Debbie,

 

Thanks for the question. Currently Smartsheet doesn't support referencing data in other sheets in formulas, however this functionality is being worked on and we hope to release in Q1. However this could change, so we don’t have an exact date at the moment. 

The workaround for this currently would be to either move/copy the lookup table of information onto the same sheet as the formula, or use cell links to create a lookup table on the same sheet as the formula. More on cell linking can be found here if needed (https://help.smartsheet.com/articles/861579).

Debbie Sin

Oh! hopefully Smartsheet can support that in Q!

However, I have another question, I am using the same formula that I use in excel but it doesn't work in smartsheet

=INDEX(D:D,MATCH(M1&M2,B:B&C:C,0))

 

It shows an error of UNPARSEABLE, why is that?

 

Robert S.

Hello Debbie,

 

The MATCH() function within Smartsheet doesn't currently have the ability to search for multiple criteria as this formula suggests. I would recommend adding your vote for this feature by using the "Submit Product Enhancement Request" link to the right in the "Quick Links" section.

 

If you'd like to write this formula with one criteria, it's similar to how this would be written in excel. Here's a few examples of this:

 

=INDEX(D:D, MATCH(M1, B:B, 0))

=INDEX(D:D, MATCH(M2, C:C, 0))

 

If you'd like to be able to search multiple columns in your formula, a workaround for this would be to create a new column that concatenates column "B" and column "C", we'll call this column "E". Here's the steps for this process:

1. Create the new column and put this formula in the first row =B1+C1
2. Drag-fill this down the rest of the sheet (https://help.smartsheet.com/articles/518318#dragfill)
3. Change your INDEX formula to this: =INDEX(D:D, MATCH(M1 + M2, E:E, 0))

This will work if your cell values are text, however if they're all numbers, you'll want to use slightly different formulas:

1. Create the new column and put this formula in the first row =JOIN(B1:C1)
2. Drag-fill this down the rest of the sheet (https://help.smartsheet.com/articles/518318#dragfill)
3. Change your INDEX formula to this: =INDEX(D:D, MATCH(JOIN(M1:M2), E:E, 0))

This uses the JOIN() function to combine a range of cells together (https://help.smartsheet.com/function/join). Keep in mind that this is assuming that the referenced ranges are contiguous. 

maberry34826

Is this type of function supported now that cross sheet referencing is available?

John Lalli

Also would like to know.  thanks