I am running in circles trying to solve this issue - seemingly, in using MATCH() on an external cell reference, the value it returns caps itself at 14 even though data goes out to ~30. While I end up using this base function in much larger equations, the screenshots explain the basis of the issue:

Here is the Cell Reference Setup... all of column [Site] which has ~30 values:

I am trying to use a =MATCH([Site ID]@row , {PlanGrid Metrics Site})....

For example purposes, I am going to hardcode in Site ID:

We can see we get "7" as expected.

But trying anything past entry 9 or "FH" faults:

However, back checking the data incoming with INDEX(), it should be working:

I have tried resetting the cell reference with no luck. Does anyone know why this is happening?

The faulting MATCH() values do not seem to follow any pattern either:

Correct for external reference rows 1-8

For MATCH attempts rows 9-14 it returns "2"

For MATCH attempt row 15 "SCCC" we get a correct 15

Anything past "SCCC", MATCH returns a "14".

What adds a level of confusion is I can throw in values not within the external cell reference and get a return value of 2:

Any help is welcome

Best Answer


  • heyjay
    heyjay ✭✭✭✭✭
    Answer ✓

    Add zero for the third arguement on the MATCH formula.

    =MATCH("This Item",{From this column},0)


  • @heyjay Thank you thank you.... I wonder why that makes such a difference for a 1-dim array/column of data, and the erroneous return values were gathered from the void I guess :p - thank you again!

  • heyjay
    heyjay ✭✭✭✭✭
    edited 02/22/24

    While it is optional and not required, when not selected, it defaults to 1 which is explained below:

    search_type—[optional] The default is 1. The manner in which to search, depending on whether the range is sorted ascending (1), not sorted (0), or sorted descending (-1).

    1: (The default value) Finds the largest value less than or equal to search_value (requires that the range be sorted in ascending order)

    0: Finds the first exact match (the range may be unordered)

    More here.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!