Aggregate Formula in Google Sheet using wildcards

Question

I'm trying to copy and paste an excel formula I use to categorize data into Google Sheets and struggle as the formula doesn't work.

I have two sheets:

The Referral sheet contains all the referral sources such as google etc. The Categories Sheet contains all the categories that I want to use and the sources within them.

So the categories sheet is formatted as follows:

Column A     Column B  
Source       Categories  
*google*     Search 
*facebook*   Social Media 

I thereby use the * as a fuzzy search within an url.

In excel I use the following formula that works

=IF(A4="","",IFERROR(INDEX(Categories!$B$2:$B$113,AGGREGATE(14,6,--(COUNTIF(A4,Categories!$A:$A)>=1)*ROW(Categories!$B:$B)-1,1)),"Others"))

Can you help me on how I need to re-write it that it also works for Google?

Answer 1

The problem with your Excel's formula is that AGGREGATE is not a Google Sheets function. While both apps belongs to the same "family" and there are a lot of common functions / features there are other that are unique to each app.

Maybe QUERY could work for you

Related

• Using AGGREGATE formula on Google Sheets - Finding the correct result based on 3 columns of criteria

Reference

• Google Sheets function list