I am trying to search a bill of materials for the next part in a product tree to identify the first "end item" underneath a parent part if there is one available. Here is the index and match formula that I have so far. The issue that I am having is that in certain cases, it is jumping to different branches of the product tree based on based on the part level meeting the criteria of the search. Is there a way to have the search stop once it reaches the end of a current branch?
Example: AG40319 returns value from row A40378 when I would like it to stop searching at row 40342 and return nothing due to reaching the end of the branch without finding an end item.
Sorry for the screenshot instead of a sample sheet. I wasn't able to get the sample sheet add-in to work.
Excel Formula:
=IF(AND(AB40319=FALSE,INDEX(A40319:A85799,MATCH(TRUE,AB40319:AB85799,0)) > A40319),INDEX(B40319:B85799,MATCH(TRUE,AB40319:AB85799,0)),"-")
Example: AG40319 returns value from row A40378 when I would like it to stop searching at row 40342 and return nothing due to reaching the end of the branch without finding an end item.
Sorry for the screenshot instead of a sample sheet. I wasn't able to get the sample sheet add-in to work.