INDEX MATCH error

I am having an issue with an INDEX MATCH formula, but I don't think it is the formual itself. I have sheet with a column to match (Order Number) to another sheet and return a different data set, which is exactly the formula I have and its working great.

However, I went to create a different INDEX MATCH formula in another column on the same sheet also matching the Order Number column on that sheet, and it is returning nothing but #NO MATCH. But I know that is wrong because I can see matching Order Numbers.

In case my formual is bonkers, it is below.

But my larger question is, can I have two different INDEX MATCH formulas matching the same row on the sheet? I can't think of what else is my issue.

=INDEX({Storage fees billing Range 2}, MATCH([Sales Order #]@row, {Storage fees billing Range 1}, 0))

Find more posts tagged with

Accepted answers

All comments

Jaime M.

Hey Jared,

Did you copy/paste in your match formula from the other sheet? If so, Smartsheet doesn't like that, and to get it to work, you usually have to go in and manually make the references to the other sheet again. Could you provide a snip of your formula, reference sheet, and the columns in the sheet you are trying to add the formula? My formula is shown below and has extra parentheses around the MATCH formula (but appears these are not necessary)- based on this your formula appears correct as well (not bonkers!).

In order for MATCH to work, they have to be exact, so the field in Range 1 you are referencing would have to match exactly what is indicated in your Sales Order # field. I have found that even spaces can throw this off.

Tested your theory about having two columns with the MATCH formula that match the same row, and it worked fine in my sheet, so I also don't think this is the issue.

Jared F

I did some troubleshoot and its not my formula, the issue is the reference sheet where my data is coming from. I created the sheet via importing an excel doc, so something is wrong with the cells even though they LOOK fine. I tested this by added a new row and the formula worked great. I am in the process of tweaking the sheet which will hopefully correct the issue. I have imported few excel sheets, but I think I will just avoid that in the future so I don't this issue.

MichaelTCA

Hello,

Manually importing data will cause this issue. Column types might not be the same between the target and source file. That alone will cause an error. Formatting can also be an issue if you are trying to upload a variable type that doesn't exist in a sheet, such as time (even though dates do exist).

Use data shuttle to update the sheet from the source file. You can add expressions in data shuttle to make a cell function or use a column function in the sheet. The trick to using lookup functions in data shuttle is to get all the references and functions working in the sheet before copying it over to the expressions.

If you want to reuse the source file instead of changing it every time in data shuttle, you can save over (overwrite) the file with the same name and in the same file location, with the new data contained within. It will continue to run like nothing changed.

If you have to manually import the files, make sure all the column types are correct before setting up the functions. In BOTH the target and source files that you're linking cells within.

Jared F

I just used Data Shuttle to performa an update and it worked, so I know my formula is solid. The issue was with the cells when I created the sheet from an excel, although I still don't konw what the issue was, using Data Shuttle made it go away, and since that is how I will update my sheet monthly from here on it, problem solved!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.