Index match returns a wrong value

kyle.macleod
kyle.macleod ✭✭✭
edited 12/09/19 in Formulas and Functions

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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!