Complex Index and Match

Josh Reed

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,

Josh

Paul Newcome

Your original formula may have worked if you had used a 0 (zero) in the third portion of the MATCH function (where you now have the -1). Zero provides for an exact match. -1 looks for a close match in descending order and 1 will look for a close match in ascending order.

Josh Reed

wow... I think I fixed it but I'm even more thoroughly confused... See below

so now I'm matching the same row, in descending order... and somehow that works? confuddling

Paul Newcome

Your original formula may have worked if you had used a 0 (zero) in the third portion of the MATCH function (where you now have the -1). Zero provides for an exact match. -1 looks for a close match in descending order and 1 will look for a close match in ascending order.

Josh Reed

@Paul Newcome yep you are absolutely right. I replaced the -1 with 0 and it still functioned properly. I thought I tried that and it didn't work. I think I had brain fog Friday afternoon, the descending part was confusing me.. in my mind ascending should have been the option that worked if it scans the column top to bottom but maybe I need to do more research on how that function actually executes.

Thanks!

Paul Newcome

Friday Fog is all too real. Haha.

Descending would be from top to bottom as it is in relation to the sheet structure (top to bottom) and not necessarily higher row numbers compared to lower row numbers.