VLOOKUP Multiple Matches

Paul Newcome

COLLECT is basically like adding an IF to any function.

=COLLECT(range_to_collect, criteria_range_1, criteria_1, criteria_range_2, criteria_2, ............................)

Dennis Wierzbicki

https://community.smartsheet.com/discussion/comment/251389#Comment_251389

Paul, I understand the IF function very well, but the COLLECT seems different. Everyone who defines it uses the word collect in their definition, or repeats the syntax. Is there a plain English description of this function, like "looks at the within the stated range, compares them to the criteria listed, and returns the largest value...", or something like this?

Paul Newcome

It pulls together ALL values from the first range based on the range/criteria sets.

When you nest it inside of another function, that function will only evaluate the data that was pulled by the COLLECT function.

Think of it almost as a JOIN function but on the back-end and with the ability to add criteria, and then the main function evaluates the "text string".