Complex Index and Match


Really banging my head against this one... I thought maybe the issue was that you can't use index and match with columns populated by formulas because it may not be the same type of content (IE text or number)... so I tried adding helper columns to convert to text = "'" + (previous coulumn @row)

no dice

Tried using =VALUE(previous column @row) that formula didn't even work so definitely no dice.

So I'm back to the drawing board. Can I not index and match cells/columns populated by formulas? that sucks if so. The situation is complicated, I can elaborate if necessary but here is the formula

SO... firs the if statement. column "TA" is an abbv for therapeutic area, this will be obtained on an intake sheet. For this build we are mashing what should be two trackers into one, and NOT using hierarchy. So only EVENTS will be entered by the intake form. There is a checkbox to the far left that is used to indicate if a row ISNOT an EVENT but rather a CONTRACT belonging to that event.

So, first part of the if statement just says if that box ISNOT checked then spit out the TA@row since it will be populated, if it IS checked do the formula

Formula: Indexing the TA column (since that's the data I want to populate), matching Event ID@row (because this will be made the same for every contract that belongs to this event, manually) against the whole AutoID column as only the EVENT level row will have the right ID since it is an autonumber column.

This results in #NO MATCH... for why? I tried the same with a VLOOKUP and again #NO MATCH

This led me down the path of trying to make sure the index and match pieces were all text, or all a value as maybe that was the issue. Am I missing something obvious here? It's been a long week but this one has really stumped me.

I pray to the Smartsheet gods for an answer,


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!