Issue with cross-sheet =INDEX(MATCH()) formulas

egardner

I am consistently coming across an error where my cross-sheet referenced =INDEX(MATCH()) formulas are pulling the incorrect information. I am fairly certain my syntax is correct and that this is a bug. This is happening across multiple sheets I have. I have included one example, but note that I have found this to happen across the board.

Here's what I suspect might be happening:

1) formula is weirdly interpreting dash symbols ("-")

2) formula is not correctly looking for exact matches, but rather partial matches.

3) formula is not always matching characters sequentially/in the right sequence.

Example:

I have two sheets: Product Codes and 2024 Guided Ops Metrics.

In 2024 Guided Ops Metrics, I am trying to populate the "Unique Identifier #" column from the "Product Codes" Spreadsheet, where the TourCode & Year @ row (Ops Metrics) matches the TourCode & Year (Product Codes).

In the Product Codes Spreadsheet, I have our 2023 and 2024 products built in the "TourCode & Year" column. Each value in the Product Codes "TourCode & Year" column is unique - there are no duplicates. Example of how they are formatted:

ACB-2023

ACB-2024

FR-NORMBRIT-2023

FR-NORMBRIT-2024

Here is the formula I used:

=INDEX({Product Codes Tour Year & Code Unique Identifier}, MATCH([TourCode & Year]@row, {Product Codes TourID and Year}),0)

This seems to be incorrectly matching the data between the two sheets. Everything that I have referenced tells me that the syntax is correct (please tell me if it's not!)

When I am trying to pull based on TourCode & Year matching ACB-2024, (WHERE Product Codes.[TourCode & Year] = Ops Metrics. [TourCode & Year]), it is incorrectly pulling the information for ACB-2023.

Similarly, for our TourCodes whose formatting contains two dashes (ex. FR-NORMBRIT-2024), it is incorrectly matching. It seems to be matching these to the highest cell on the sheet that matches first couple of characters in the cell.

I have tried reversing the position of the year and tour code on both sheets (example: 2024-ACB or 2024-FR-NORMBRIT) but it still incorrectly matches the Tour Codes formatted with two dashes.

I have tried the following (among other things) to troubleshoot:

• "Manage References" > clearing all references and re-writing the formulas,
• Checked that match formula was referencing the whole column on ProductCodes spreadsheet
• Copied and pasted exact values from sheet one to sheet two to ensure they exactly match. (ex. copying "ACB-2024" from my target sheet to my referenced sheet in the appropriate cell to ensure they were exactly identical.
• Switched the position of the year (ex. -2024 vs 2024-)
• Referenced and read through following Smartsheet pages: "INDEX Function," "MATCH function," "Formula combinations for cross-sheet references," "INDEX and MATCH across two sheets: a detailed explanation," and "Video Index Match Tutorial."
• Sent help ticket to Smartsheet. Recieved only generic "have you references these resources?" response and did not respond when I replied I had.

Is this a bug? If it is a bug, who can I reach out to to get it fixed? Am I misinterpreting the correct usage of =INDEX(MATCH()), or using incorrect syntax? Has anybody come across this and/or have a solution/workaround?

Cheers,

Emma

Paul Newcome

The problem is with a misplaced parenthesis. That zero at the end is technically part of the INDEX function, but you want it as part of the MATCH function to denote an exact match.

You have:

INDEX(.........MATCH(...........), 0)

You want:

INDEX(.........MATCH(..........., 0))

StevenBlackburnMBA

I see the potential issue but I'm not too sure yet and want to try to replicate it on my end.

Can you send the formula copy and paste here so I can manipulate it a tad, I believe it may have something to do with your range but will verify.

Are you able to send a picture of how you selected your ranges in the cross sheet reference?

StevenBlackburnMBA

Also, would be glad to help interpret... could hop on a call and assist or am willing to be shared on a duplicate of the sheets in a separate workspace with all unnecessary and non needed information deleted from the worksheet? That might be a good way to see your reference ranges and how you have this sorted. Please let me know, I'd love to help.

egardner

Hi Steven,

Thanks for getting back to me.

I've created a workspace that I can share with you with two scrubbed versions of the documents, if that's helpful. Let me know how you'd like me to go about sharing that with you. Also more than happy to hop on a call.

Here's the formula (from the scrubbed version):

=INDEX({Product Codes Unique Identifier}, MATCH([TourCode & Year]@row, {Product Codes Tour Code & Year}), 0)

I normally just select the entire column when selecting the range. I've also tried selecting all populated cells (using shift + click) instead of clicking the column header, but it made no difference.

I've also re-named the selection ranges just for my own purposes. In troubleshooting, I also tried going in and deleting the reference ranges, re-doing the formulas with the default range names to see if it made a difference, but still got the same result.

See below for photos of the referencing process.

Thanks so much for your help!

Emma

Paul Newcome

The problem is with a misplaced parenthesis. That zero at the end is technically part of the INDEX function, but you want it as part of the MATCH function to denote an exact match.

You have:

INDEX(.........MATCH(...........), 0)

You want:

INDEX(.........MATCH(..........., 0))

egardner

Hi Paul,

I had tried that originally when troubleshooting and it didn't work. I just tried that again and that fixed it. Thank you so much!

Paul Newcome

Happy to help. 👍️