Does the [ search_type ] difference in MATCH affect performance?

I am trying to consider why I would use 1 instead of 0, and I can only think that it must be a performance issue.

This assumes that search_type 1 uses divide and conquer, where as 0 searches linearly.

I have had situations were using 1 gets a bad result if the lookup range gets sorted, so I make sure I use 0 to mitigate that. And I want to make sure that I am not overlooking someone other downside to using 0.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    1 and -1 give an approximate match whereas 0 gives an exact match.


    Smartsheet calculates the relative position of a search value by counting cells from left to right (across columns), then top to bottom (across rows). In a lookup table consisting of two columns, the cell in the top row of the leftmost column is the first position, 1.

    For the optional [search_type] argument:

    • 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 find the first exact match (the range may be unordered).
    • -1 finds the smallest value greater than or equal to search_value (requires that the range be sorted in descending order).


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!