Getting answer even when all cells are blank

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.

Find more posts tagged with

Accepted answers

KPH

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)

All comments

KPH

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

That is exactly what was missing. Thank you

LDLValentine

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

KPH

Great! I'm glad you have that sorted.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.