Index/Match is almost completely broken

Hi guys, I don't really know what's happening, and it's honestly the first time that it's happening to me.

I have used this specific INDEX/MATCH syntax without issue, up until today.

=IFERROR(INDEX({Office Time Zone}, MATCH([Office Number]@row, {Office Number Ref Sheet}, 0)), "")

It's doing what I told it to, which is pulling the TZ by office, using the office number. But it's off. WAY off. You can catch a glimpse of it in the above screenshot. I'll share with you other screenshots with further details.

This is the destination sheet. California only has Pacific, but is pulling Central. Same goes for Florida, Eastern but is pulling Pacific. Etc.

2021-11-23_10-06-00.jpg

The above is the source sheet. Which, as you can see, is all correct.

What really boggles me, is that when I change the TZ on the source to something else, say for the 678 office, the destination does not update the change to reflect it.

I am really confused right now. Any help is greatly appreciated.

Find more posts tagged with

Accepted answers

Faaez Kamaal

Hi Genevieve, I set up even more formulas to help me troubleshoot.

Firstly, the MATCH alone is not matching on the row ID. This is not the expected behavior that you outlined earlier.

Secondly, I threw the INDEX back on, after concatenating the office number to the time zone, that's not matching with the 2 different source sheets.

Lastly, I flattened the source sheet references (pulling the office number and the required data from the same sheet) with the concatenated information and... it's working? It was not working when I did this the other and my prompt to create this thread/submit a bug report.

2021-11-29_09-11-45.jpg

2021-11-29_09-00-04.jpg

2021-11-29_08-59-38.jpg

2021-11-29_08-59-24.jpg

2021-11-29_08-59-01.jpg

2021-11-29_08-56-59.jpg

2021-11-29_08-56-05.jpg

2021-11-29_08-55-11.jpg

2021-11-29_08-09-23.jpg

All comments

Bstar

Hi Faaez,

It looks like in the INDEX function you left out the column you want to pull the data from:

=IFERROR(INDEX({Office Time Zone}, MATCH([Office Number]@row, {Office Number Ref Sheet}, 0) ,5 ),"")

I think if you just add the " ,5 " after the MATCH function it should fix the problem.

Faaez Kamaal

Hi BStar,

Unfortunately, that did not work. I do not get a result when I specify 5 in the column index. Essentially, a blank cell.

I use this formula (with other columns) across at least a dozen working sheets without needing to specify the column index. So, I'm not exactly sure why it's broken here.

Faaez Kamaal

I did some more debugging, and I found something completely inexplicable. 810 does NOT exist on the source sheet.

Please help.

Genevieve P.

Hi @Faaez Kamaal

810 is referring to the Row Number where the Match is found.

MATCH will find the row where [Office Number]@row is associated in the {Office Number Ref Sheet}

Then the INDEX function needs the column to return and the row number to bring data back from...like so:

=INDEX({Column to Return}, Row Number

That's why we replace Row Number with the MATCH function, as it returns the number:

=INDEX({Column to Return}, MATCH(Value@row, {Column With Value}, 0))

What is the value in row 810? Is it 895? What happens if you try adding in a 1 instead of a 0, to indicate that your numbers are Sorted Ascending?

Faaez Kamaal

Hi @Genevieve P. ,

Thank you for taking the time to respond.

The source sheet is sorted by the Office Number column in the Office Time Zone source sheet, as is the Office Number column in the source sheet for Office Number Ref Sheet. To clarify, I'm pulling the same Office Number column from 2 different sheets.

I did flatten my logic a little bit, and had the index and match come from the same source sheet. And, that produced different, yet similar results. The Office Number on the destination sheet, does NOT match what's coming from the source sheet.

The 2 outer columns are the office numbers, and they SHOULD match. The right most column is what I want to use for the time zones.

The 3 middle columns are Office Name, Office State, and Office Region. These 3 columns are using the exact same INDEX/MATCH syntax as I shared in the first post, and have no issues. These match perfectly.

However, the right and left columns do NOT match, given the fact that I'm using the same formula syntax.

On top of all of that, 810 as I stated earlier, does NOT exist in either source sheet. So where is that coming from?

I have submitted a bug report, as I feel that this is genuinely broken.

Genevieve P.

Hi @Faaez Kamaal

Can you post a screen capture that includes the row number from the source sheet as well? The MATCH function is returning the Row Number.

For example, if I'm looking for a match for the word "Test" and "Test" is in row 3, just using MATCH (without INDEX) will bring back the number 3, since that's the Row:

Screen Shot 2021-11-25 at 7.41.16 PM.png

However when you use INDEX, this then allows you to bring back a cell value from a specific column from that row.

What I'd like to know is what row number 810 is housing in the other sheet. Is that row a match? Or does that row have a different number than what you're Matching?

It would also be helpful to know how the Source Sheet values are being populated - I wonder if they are being recognized as text instead of numerical and that's changing things up. Can you post screen captures of the Cross Sheet Formula Reference window open, identifying the column you're using for the {Office Number Ref Sheet} reference?

Thanks!

Genevieve

Faaez Kamaal

Hi Genevieve, I set up even more formulas to help me troubleshoot.

Firstly, the MATCH alone is not matching on the row ID. This is not the expected behavior that you outlined earlier.

Secondly, I threw the INDEX back on, after concatenating the office number to the time zone, that's not matching with the 2 different source sheets.

2021-11-29_09-11-45.jpg

2021-11-29_09-00-04.jpg

2021-11-29_08-59-38.jpg

2021-11-29_08-59-24.jpg

2021-11-29_08-59-01.jpg

2021-11-29_08-56-59.jpg

2021-11-29_08-56-05.jpg

2021-11-29_08-55-11.jpg

2021-11-29_08-09-23.jpg

Genevieve P.

Hi @Faaez Kamaal

My apologies for the delay, I missed this reply! Thank you for all the screen captures.

I see that in the cross-sheet column {Office Number Ref Sheet} that the numbers are appearing on the left of the cell, whereas in your current sheet in the Office Number column your numbers are appearing on the right of the cell. This indicates that the values are being seen differently... in one column they're seen as text and in another they're seen as numerical values, which may be why it's not finding the match correctly.

I'm glad that it's working now - it sounds like re-doing it may have made the values match up... are you still having issues?

Faaez Kamaal

I'm glad it's working now, too. No, no more issues. :) Thank you.

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.