Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Index and match functions

Debbie Sin
edited 12/09/19 in Archived 2017 Posts

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

 

Comments

  • Robert S.
    Robert S. Employee

    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).

  • 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.
    Robert S. Employee

    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
    maberry34826 ✭✭✭
    edited 08/10/18

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

  • John Lalli
    John Lalli ✭✭✭

    Also would like to know.  thanks

This discussion has been closed.