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
Comments
-
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?
-
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:
- Create the new column and put this formula in the first row =B1+C1
- Drag-fill this down the rest of the sheet (https://help.smartsheet.com/articles/518318#dragfill)
- 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:
- Create the new column and put this formula in the first row =JOIN(B1:C1)
- Drag-fill this down the rest of the sheet (https://help.smartsheet.com/articles/518318#dragfill)
- 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.
-
Is this type of function supported now that cross sheet referencing is available?
-
Also would like to know. thanks
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives