Index match returns a wrong value

kyle.macleod

Anyone able to help with an Index/match formula that returns a wrong value?

 

We've a formula that is returning a wrong value. I believe the formula is correct (it's the same way I write it in .xls). We are referencing another Smartsheet in the formula.

 

I understand if I was getting an error (#invalid value, #No match), but here we are getting an expected result, but it is the wrong value.

 

Formula: =INDEX(({SPB 2019 Range 1}), MATCH([SPB #]1, {SPB 2019 Range 2}), 1)

(I've tried removing the last 1 to no difference).

 

If it helps:

SPB2019 Range 1:  Dropdown List

SPB#1:  Text/Number

SPB2019 Range 2: Auto-number

 

In this case we're getting a possible value, "completed", even though the correct value is actually "executing"

 

A second issue is that we're quickly running into the limit of total number of links, even though we're no where close to the advertised limit.

 

We've spent a whole lot of time trying to fix this.  Hopefully someone can help, because I'm apparently not smart enough.  Thanks in advance.

Andrée Starå

Hi Kyle,

Try changing the 1 in the end to 0 instead.

Did it work?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

kyle.macleod

Hello Andree, sadly that didn't work.  I also tried no number, 2, -1....

 

Thanks for trying, but my problem persists.

 

-Kyle

Paul Newcome

Do you have any empty or unused rows? Maybe a header row at the top? Try changing the value you are matching on and see if there is a consistent difference. Maybe its pulling the value from say 3 rows above the correct value every time or something to that affect.

Andrée Starå

Happy to help!

I agree with Pauls comment below.

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

kyle.macleod

There WERE blank rows in the SS data we linked to.  Also added the ,0 to the end of the equation:  

=INDEX({SPB 2019 CY Savings}, MATCH([SPB #]1, {SPB 2019 Range SPB}, 0))

 

Here's the rub.  Initially after removing empty rows in the referenced sheet and adding the "0", the formulas worked correctly.

 

30 mins later values are corrupt again.  FWIW, I only have View access to the referenced sheet (admin to mine).  Also there are hundreds of people with access to this referenced sheet, with probably 5-10 at any given time making updates.

Any ideas why they would work then mysteriously not?

 

kyle.macleod

here's a screenshot in the format this forum enjoys.

kyle.macleod

The blank row elimination from the referenced (view only) sheet, along with the addition of the ,0 at the end of the formula seems to have corrected the formulas, at least temporarily.  It was good for about 30 mins until someone else added blank rows again.

It'd be great to have a method to make the formula insensitive to extra rows.  

 

Thanks for the information & assistance.

Paul Newcome

Ok. So I am going to go ahead and apologize in advance. I am still trying to shake off "vacation brain", so I am having trouble getting my head back to where it was when I asked about the blank rows. I have a solution, but you are going to have to play with it a little to see which way works for you.

.

Use a COUNTIFS to basically count the blank rows and then add or subtract this number accordingly from the number produced by your MATCH function that determines the row number for your INDEX statement.

 

Something like this...

 

=INDEX({SPB 2019 CY Savings}, MATCH([SPB #]1, {SPB 2019 Range SPB}, 0) -  COUNTIFS({SPB 2019 Range XYZ}, ISBLANK(@cell)))

 

You may need to change the - to a +. You may also need to incorporate the number 10 either adding or subtracting as well as there are a default of 10 blank rows at the bottom of every sheet.

.

Again... I'm sorry I can't be more specific as to the actual solution. I spent 3 days in a kayak on the river, and my brain is still fighting this whole "work" thing. The above is the general idea though.

 

Let me know if you can get it working. If not, I will check back in later once I have come to terms with the fact that I have to sit behind a desk instead of behind a fishing pole. Haha.

Kayla Q

@Andrée Starå and @Paul Newcome I'm going to chime in here because I'm experiencing a similar issue.

I am using the following function: =IFERROR(INDEX({Matters Report Range 1}, MATCH([Legal Tracker Matter Tracker ID]@row, {Matters Report Range 2}), 0), "NOT IN OMR")

41032184 is not present in the lookup sheet (Matters Report), so this should return "NOT IN OMR."

However, it is returning the value associated with lookup value 41010316.

I tried changing the 0 to a -1, and that worked, but it messed up the rest of my INDEX/MATCH functions. What on earth is going on? How do I ensure consistency in all of my functions?

EDIT: If helpful, the same thing is happening on rows where there is no match and I would expect "NOT IN OMR" to return. Maybe this doesn't play nice with IFERROR?

EDIT (again): I removed the IFERROR, and I'm still getting the same incorrect return value.

Paul Newcome

@Kayla Q It looks like the Lookup value in the first screenshot is being stored as a text value (unless you formatted the cells). Is it possible you have some text and some numerical values in the same column of the reference sheet?

Kayla Q

Hi @Paul Newcome

I think this is set to a number format (see below). There is no ' in front of the numbers indicating a text format. Is there anything else I need to do?

Kayla Q

@Paul Newcome I figured it out! My "0" was in the wrong place -_-

Paul Newcome

@Kayla Q That would do it. Sorry I missed it too, but glad you were able to find it.

AbrinaLL

I had a problem when using INDEX and MATCH function. My function wrapped in an IFERROR was producing a number but not the right one. I figured out that Smartsheet's has default values for optional Syntax; in this case it was default to 1 for sort acceding, and 0 for not sorted. Since I didn't add a ,0 to my MATCH function at the end like I did for the INDEX when trying to fix my problem originally, the function still displayed the wrong numbers until I added the , 0), 0)

=IFERROR(INDEX({Weekly Bin Count}, MATCH([Store Number]@row, {Order to Store}, 0), 0), "No Order")

Paul Newcome

@AbrinaLL You don't need the zero in the INDEX function if you are referencing single columns. The one that makes the difference is the one in the MATCH function.