Working with a Lookup Table
I have a Smartsheet with 8 columns. The first column is a primary column that holds no data and is pretty much a placeholder. The second column uses a drop down menu with various ink names. The other 6 columns are the names of compounds that should populate with a percentage or an 'X' based on the item selected in the second column from the dropdown menu.
I have created an associated lookup table that consists of 7 columns, with the first column consisting of the various ink names and the other columns are the names of the compounds and their percentages based on the ink names.
The sheet column listing looks like this:
Job Number| Inks Used| C1| C2| C3| C4| C5 | C6
The lookup table looks like this and is titled 'Sop Additives Lookup Table'
Inks C1 C2 C3 C4 C5 C6
PMS 2746 3% 1% 1% 1% X X
PMS 2766 1 3% X 1% X X X
PMS 2766 2 3% X 1% X X X
Clear/Color 3% 1% 1% 1% X X
White 2% X 1% X X X
Blocker 1% X X X X X
Adhesive X X X X X X
When trying to input the formula =INDEX('SOP Additives Lookup Table'!B:G, MATCH([Inks Used]@row, 'SOP Additives Lookup Table'!A:A, 0))
I am receiving an #UNPARSEABLE error. Any help would be much appreciated!
Answers
-
The formula you are using looks like one you would use in Excel rather than Smartsheet. If your formula uses inter-sheet references you will need to create the reference range to link to. You can click on the "Reference another sheet" seen in the picture below.
When you are done the formula will look like:
- =INDEX({SOP Additives Lookup Table Range 1}, MATCH([Inks Used]@row, {SOP Additives Lookup Table Range 2}, 0))
Best,
Zach Hall
Training Delivery Manager / Charter Communications
-
Hi Zachary,
Thank you for getting me on the right track! So, for the SOP Additives Lookup Table Range (both 1 and 2) what would be the syntax for that formula?
-
Hi Zach,
Ignore that last message. I see that I just have to make sure that I choose the correct data range. I'm still working on it so thanks again!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!