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
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?
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:
This will work if your cell values are text, however if they're all numbers, you'll want to use slightly different formulas:
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.
Is this type of function supported now that cross sheet referencing is available?
Also would like to know. thanks