Getting answer even when all cells are blank

Options

IFERROR(INDEX({Req Main Sort Summary}, MATCH($[LIB ID]@row, {LIB ID}, 0), MATCH([Script Name]$1, {Req Main Sort Header})), "")

Two sheets -

First is the primary sort with Script Numbers, Names and other key information

Second is the primary Test Build / Tracker sheet where End2End tests are built from the sort sheet.

There are 300+ tests to build up integrated scenarios from. The second sheet also tracks defects, status and other critical test metrics.

There should only be data in a Script name field if there is data in the LIB ID field, However, unless I clear the field of the formula there is a random (last script) filled down as far as the formula is filled down. This is very confusing to the user as this needs to be dynamic.

The Defect Index and Match calculation does something similar, though it is going out and simply pulling the first thing it finds.

Any suggestions as to what I am doing wrong would be most helpful.

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    In the second match, the search type is not specified so it will use the default type, which is 1 (see below). Does changing this to 0 help?

    IFERROR(INDEX({Req Main Sort Summary}, MATCH($[LIB ID]@row, {LIB ID}, 0), MATCH([Script Name]$1, {Req Main Sort Header},0)), "")

    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: Finds 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)


Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    In the second match, the search type is not specified so it will use the default type, which is 1 (see below). Does changing this to 0 help?

    IFERROR(INDEX({Req Main Sort Summary}, MATCH($[LIB ID]@row, {LIB ID}, 0), MATCH([Script Name]$1, {Req Main Sort Header},0)), "")

    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: Finds 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)


  • LDLValentine
    LDLValentine ✭✭✭✭
    Options

    That is exactly what was missing. Thank you

  • LDLValentine
    LDLValentine ✭✭✭✭
    Options

    That is exactly what was missing, thank you. Solved the problem in both formula's.

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Great! I'm glad you have that sorted.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!