The purpose of this function is to act as multiple vlookups to pull all data that has multiple matches, and allows the user to specify how they want the data delimited
The function is called EXTRACTALL and it has four arguments:
Argument 1: Looup Field. This is the value that you are looking for. If it is text, make sure it is in quotes
Argument 2: Lookup Range. The range the function is going to search to find the value specified in argument 1
Argument 3: Output Range. If a match is found in the lookup range, this is what will be returned. The ranges should be the same length
Argument 4: Separator. This is how the results will be separated.
An example of the formula is below:
This will look for the letter A in column B, and when there is a match the value from column A will be pulled. All values will be separated by commas.
You will note the values that correspond to A’s are 1, 3, 5, 8, and 9 which is what the result displays in cell D11. This acts effectively the same as a lookup function might with the difference being this will pull every match and put it into one cell whereas a lookup will just grab the first match and nothing else. This is more useful in the case of text values because after using this formula (especially if a comma separator is used) you can convert from a comma delimited field into multiple fields.
Below is the code: