Index match returns a wrong value

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.

Comments

  • Andrée Starå
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

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

     

    Thanks for trying, but my problem persists.

     

    -Kyle

  • Paul Newcome
    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.

    thinkspi.com

  • Andrée Starå
    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, [email protected])

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • kyle.macleod
    edited 09/08/19

    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?

     

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

    screenshot SS.png

  • 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
    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.

    thinkspi.com