Why doesn't INDEX(MATCH) work as intended?

ChristineNDI

Hi, all: I've recently been assigned to work on a set of tools for my team that have been run in Smartsheet for some time, and am really struggling.

Setup: 4 sheets, for 4 separate project-cycle teams. Each sheet needs to refer to the same core set of unique IDs entered in the primary column for Sheet 1. All sheets contain references to other columns from Sheet 1, and/or columns from other sheets. In the past, this was done by using cell linking - Sheets 2, 3, and 4 each had a primary column linked from the primary column in Sheet 1, and then other columns linked in from various sheets.

This seems like a parallel to using array formulas in e.g., Google Sheets, which won't work for our needs: new IDs will be added and sorted into Sheet 1, and info in rows in Sheets 2, 3, and 4 must be associated with the unique ID in the primary column. If Sheet 1 is re-sorted, my understanding is that the order of unique IDs and associated dates, notes, progress steps, etc., will change, but the record info entered in specific rows in Sheet 2, 3, and 4 won't respond to the re-sort, since their linked columns are completely independent of row/record content. (If this is not the case in Smartsheet, I'd be glad to learn it! I've hit the ground running and learned everything on the fly in the last 2 weeks.)

I tried fixing things by setting up references for columns in Sheet 1 (and others) for use in INDEX(MATCH) formulas, the idea being that teams would manually enter unique IDs in Sheets 2, 3, and 4 (and then sort according to their needs), with those IDs then functioning to pull up the relevant info from other linked reference ranges. Values are showing up that don't even exist in Sheet 1.

Example: I added a new unique ID EXAMPLE to the primary column in Sheet 1 and picked a Team that this new example belongs to, which automatically populates a Team Lead email address on Sheet 1. On Sheet 2, I manually added EXAMPLE to the primary column: columns that should auto-populate Team and Team Lead email return the wrong team and the wrong team lead email. Not an error! Just completely incorrect information.

I live for INDEX(MATCH) in Google Sheets, and have been using spreadsheets as a professional for decades... I am mystified as to how Smartsheet works.

I apologize in advance that I can't share example sheets or screenshots.

Help! (And thank you!)

Paul Newcome

Are you able to provide the formula that is giving you issues?

Genevieve P.

I agree that it would be helpful to see the formula you're using!

It sounds like perhaps the MATCH function is finding a partial match, instead of an exact match. Try adding a 0 to the end of it:

INDEX({Column to Return}, MATCH("Criteria", {Column with Criteria}, 0))

Let us know if that helped 🙂

ChristineNDI

Hi, all:

Thanks for responding - the problem has disappeared as mysteriously as it appeared!

Genevieve, the syntax I used last week was exactly the same as what you've written: INDEX({Column to Return}, MATCH("Criteria", {Column with Criteria}, 0)). Didn't work then, does work now - for that at least I am grateful.