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.
Best Answer
-
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
-
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)
-
That is exactly what was missing. Thank you
-
That is exactly what was missing, thank you. Solved the problem in both formula's.
-
Great! I'm glad you have that sorted.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!