Index Match Column formula returning wrong values sometimes

Hi All

Thanks heaps for any advice.

I have a sheet with a drop down of all my products. In the next cell i have an index match formula pulling the description of the product. It was working in the initial cell so made it a column formula. IT works for everything but as i add more rows the formulas seems to pulling wrong descriptions across. the descriptions are close but not accurate.

here we see that it is pulling the same description for FT6 and FT6SW-1. the "-1" denotes single as is correctly shown in the next cell fdwe-1. I have checked the source sheet and it is correct.

Why would index match be working all the way done column til' this point. it mistakenly returns wrong description for another "-1" product aswell but all the other "-1's" products are coming across fine.

Any ideas.

thanks heaps

Find more posts tagged with

Formulas

Accepted answers

AnthroTim

Hiya,

I’m on my phone so it’s hard to read the formula, but I think you have the ,1 on the wrong side of the bracket.

It should be part of the match function, not the index function.

this means that it’s treating 0 as a column index rather than a search type.

=INDEX({Master Product and Price List Range 1}, MATCH(Product@row, {Master Product and Price List Range 2},0))

Tim

All comments

AnthroTim

Hi,

It might be worth checking the Search Type parameter in your MATCH.

I know I had a similar thing happen to me a while back because I had it set to the incorrect value (I had it set to 1 rather than 0).

See here for more info on what the various options do:

https://help.smartsheet.com/function/match

Good luck

Tim

Chris Martin

Hi Tim

thanks for responding.

My formula is

=INDEX({Master Product and Price List Range 1}, MATCH(Product@row, {Master Product and Price List Range 2}), 0)

range 1 is description, Product @ row matches the range 2 which is the SKU

Just dont understand why the FT6SW30-1 and FT730SW-1 are causing problems. FT820SW-1 returns normal.

Thanks

Chris

AnthroTim

Hiya,

I’m on my phone so it’s hard to read the formula, but I think you have the ,1 on the wrong side of the bracket.

It should be part of the match function, not the index function.

this means that it’s treating 0 as a column index rather than a search type.

=INDEX({Master Product and Price List Range 1}, MATCH(Product@row, {Master Product and Price List Range 2},0))

Tim

Chris Martin

uggh stupid amateur mistake. thanks heaps. I was being fooled as it was working 99% of the time. Seems to have fixed it. Cheers

AnthroTim

He he. No worries. Easy mistake to make, brackets and commas are always a pain.

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.