Working with a Lookup Table

ToTheMax

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!

Zachary Hall

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

ToTheMax

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?

ToTheMax

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!