IfError Index Collect with OR

BB1776

I am trying to write an IfError Index Collect formula using an Or feature.

Context of what I am trying to achieve.I would like the formula to return the Project Name (range 1) IF the Row (range 4) matches and the Title (Range 16) matches NPP Text OR IF the Row matches and the Title (range 15) matches. The reality - I am trying to return the project name if the row matches and someones name appears in the NPP range or the BSA range. They are names but they exist in a text field, so there is not an issue between text and contact field types.

I have successfully written the formula when the Or is not necessary:

=IFERROR(INDEX(COLLECT({Project Portfolio_Biffle Range 1}, {Project Portfolio_Biffle Range 4}, [Portfolio Row]@row, {Project Portfolio_Biffle Range 16}, [NPP Text]@row), 1), "")

However, I have tried a variety of ways using OR and it fails each time. Below is the most recent effort. Any help would be greatly appreciated.

=IFERROR(INDEX(COLLECT({Project Portfolio_Biffle Range 1}, {Project Portfolio_Biffle Range 4}, [Portfolio Row]@row, {Project Portfolio_Biffle Range 16}, [NPP Text]@row, OR({Project Portfolio_Biffle Range 4}, [Portfolio Row]@row, {Project Portfolio_Biffle Range 15}, [BSA Text]@row),),1),"")

I also tried a longer version but with the same error:

=IFERROR(INDEX(COLLECT({Project Portfolio_Biffle Range 1}, {Project Portfolio_Biffle Range 4}, [Portfolio Row]@row, {Project Portfolio_Biffle Range 16}, [NPP Text]@row, OR(INDEX(COLLECT({Project Portfolio_Biffle Range 1}, {Project Portfolio_Biffle Range 4}, [Portfolio Row]@row,{Project Portfolio_Biffle Range 4}, [Portfolio Row]@row, {Project Portfolio_Biffle Range 15}, [BSA Text]@row),1)))1,),"")

Here is the simpler formula that worked:

Row 1 is where the failing formula exist

Many thanks to anyone willing to help!

Paul Newcome

You wouldn't use an OR statement at all. Just the IFERROR.

=IFERROR(index_collect_1st_match_column_set, index_collect_2nd_match_column_set)

BB1776

Hi Paul - thanks for the help. This doesn't seem to work in the way I hoped. It is not yielding any results (blank, not an error) in the formula column (Project Name). Here is the current formula:

=IFERROR(INDEX(COLLECT({Project Portfolio_Biffle Range 1}, {Project Portfolio_Biffle Range 4}, [Portfolio Row]@row, {Project Portfolio_Biffle Range 16}, [NPP Text]@row, {Project Portfolio_Biffle Range 15}, [BSA Text]@row), 1), "")

Does it not cause an issue because the last two set of collect criterion are searching without some sort of "or" feature since they are searching for the same name (BSA Text @ row) in two different ranges? Is the and/or implied in the process?

PS - I have been using this community feature for years and have benefited from your solutions countless times. Thanks for sharing your knowledge.

Paul Newcome

Your syntax is off. You need two separate INDEX/COLLECTs. One for range 15 and another for range 16 (with range 4 and whatnot included in both.

Then you would use the IFERROR to join the two together.

=IFERROR(INDEX(COLLECT(everything for range 15), 1), INDEX(COLLECT(everything for range 16), 1))