Odd behavior with Index(Match

Using a basic Index(Match on 1 criteria to cross reference 2 sheets. I've checked multiple times that 1) the cells pulling in are actual numbers (no formulas), and 2) there is a MATCH to pull in.

Here is the formula - appreciate help on what I'm missing

=INDEX({Equipment Leadtime - MPTs MVA Limit}, MATCH([MPT Supplier]@row, {Equipment Leadtime MPT - Supplier}), 0)

{Equipment Leadtime - MPT MVA Limit} is this range on the source sheet. In the Source Sheet this is data that is manually inputted as #:

[MPT Supplier]@row is a drop-down cell which Data Shuttle upload populates for us. Here I've confirmed the NAMES are as brought in from the Source Sheet which is the same sheet shown above where {Equipment Leadtime - MPT MVA Limit} comes from.

{Equipment Leadtime MPT - Supplier} is literally the same cell the data shuttle creates the drop down in the target sheet above from - so the names definitely match.

What is ODD with having both sheets up to watch the results change as I change the MPT Supplier in the drop-down on the target sheet to make sure there is indeed data is some MPT Supplier names bring in the data…others give a NO MATCH even though I can see the Match.

TARGET SHEET WITH INDEX(MATCH formula

and the Match from the Source Sheet…

I'm sure this is simple but I'm on a deadline - appreciate pointing out my silly mistake.

Find more posts tagged with

Accepted answers

All comments

Jennifer Kurtz

Hi @RotFraught -

Can you make your INDEX(MATCH a column formula? I'm wondering if there is some kind of variation in your formula in the cells where it isn't finding a match…?

Another suggestion would be to double check your range references, just to make sure the ranges are capturing the whole column.

Do either of those things help?

RotFraught

https://community.smartsheet.com/discussion/comment/462870#Comment_462870

Thanks Jennifer, the column where the formula is in is a Text cell, I should have clarified in my last screen shots that the row just below where Siemens - Jundiai, Brazil (TUSA) is getting a #NO MTACH is the same INDEX(MATCH formula that is returning a data for Siemens - Tenjo, Colombia (SAT) (image below).

I checked the formula references and all are selecting the entire column and there are no "blank" rows where the formula index and match function could stop. I don't want to use VLookup but can't think of how to fix this - baffeling.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.