Cross-Sheet MATCH INDEX ERRORS

Austin Urban JCI

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

heyjay

Add zero for the third arguement on the MATCH formula.

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

heyjay

Add zero for the third arguement on the MATCH formula.

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

Austin Urban JCI

@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

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.