collect multiple information from different columns

Sabrain

I have built a formula to collect the information of a row, when a selected row, fits another row.

=IFERROR(IFERROR(JOIN(INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row)); {Probenabgabe Bereich 1}; HAS(@cell; ("" + [Material Number]@row))); 1); INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row))); 1)); "x"); "")

My problem is that there is the possibility that 2 columns from different rows could fit and i want to have both results. Now the formula only looks if there is a column to collect and only returns 1 information even if it fits to 2 possible information. How can i collect all information that fit and show it in my formula column?

Greetings, Sabrina

Genevieve P.

Hi @Sabrain

You could add another INDEX(COLLECT function within your JOIN function to bring back another value. However in this instance I would recommend using CHAR(10) as your delimiter:

=IFERROR(IFERROR(JOIN(INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row)); {Probenabgabe Bereich 1}; HAS(@cell; ("" + [Material Number]@row))); 1); INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row))); 1); CHAR(10)); "x"); "")

So if you're looking for a different value, you'd add in another statement:

=IFERROR(IFERROR(

JOIN(

INDEX(COLLECT(...); 1);

INDEX(COLLECT(...); 1);

INDEX(COLLECT(...); 1);

CHAR(10));

"x"); "")

Does that make sense?

Cheers,

Genevieve

Sabrain

@Genevieve Evans

Thank you for your answer.

I accidentally took a screenshot of the wrong formula at first . The correct formula, which iuse is:

=IFERROR(IFERROR(INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row)); {Probenabgabe Bereich 1}; HAS(@cell; ("" + [Material Number]@row))); 1); INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row))); 1)); "")

I tried to collect multiple results from index(collect) by adding:

=IFERROR(IFERROR(JOIN(INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row)); {Probenabgabe Bereich 1}; HAS(@cell; ("" + [Material Number]@row))); 1); INDEX(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row))); 1));CHAR(10)); "")

but it still does not show me all possible results that match my criteria.

Is it even possible to show multiple results by using index(match)..? ?

Greetings,

Sabrina

_Genevieve Evans

I want to make sure the right Genevieve gets the credit here. You're amazing, @Genevieve P. !

Genevieve P.

Haha, thanks for the tag and kind words, Genevieve!

Hi @Sabrain,

Perhaps I'm not understanding what it is you're looking to do; it looks like you're using one IFERROR to eliminate a criteria in case there are no matches from the first formula, is that correct?

If that's the case, and what you're asking for is if your initial INDEX formula can return multiple results, then try swapping out INDEX for JOIN:

=JOIN(COLLECT({Probenabgabe Bereich 2}; {Probenabgabe Bereich 3}; HAS(@cell; ("" + Batch@row)); {Probenabgabe Bereich 1}; HAS(@cell; ("" + [Material Number]@row))); CHAR(10))