If you know Vlookup and Index Match then you are considered to be an advanced Google Sheet or Excel user. With these functions and formula you can lookup values in column. Once you get good with these functions you can find values from columns and rows.
But if you are familiar with Filter, you know how it is easy to sort data and filter out unnecessary data. You get clarity on your data and you can narrow down your search.
Filter as function and formula is very different. Just like we use Filter feature we can use Filter function to filter out our data as well as combine it with other functions to get our desired result.
What is the Filter formula?
As the name suggests, the filter formula filters data as per the given conditions. We get filtered data to incorporate with other functions.
There are many similar functions and formulas in Google Sheets that give the same result but which formula is best is hard to decide. That’s something that you get to know when you are experienced enough.
Formulas like Vlookup and Index Match are quite similar but they have limits, strengths, and weaknesses. It also depends on how you want to build your formula, how your data is organized, and a lot of other factors.
When it comes to lookup values, I prefer Filter over Index Match and Vlookup.
Here are some reasons why Filter Function is better than Vlookup and Index Match,
- It is simple
The most important factor is, it’s simple and easy to understand.
It’s like applying filters but instead of using filters or filter view feature, we are using the function to get the desired filtered data. Once we get the range we can nest it with other formulas like Sum, Counta, etc.
- It is short
Filter function has two syntax first range that you want to be filtered and second the condition. It’s shorter than Vlookup and Index Match. Index Match and Vlookup have 3-4 syntax but Filter has only 2 and that makes it super easy to add multiple conditions.
- Segmentation Data Slicing
We can easily create multiple segments on different sheets using filter data.
This method is very useful for segmenting large information into smaller segments by creating tabs to work around.
Note: we can use slicers that are similar to filters.
- You can apply multiple conditions or criteria
The filter formula supports multiple criteria, you can have multiple criterias to filter out data. It’s best to get important and clear data from large data. You can easily get specific information with matching conditions.
- #REF error
Often we have multiple entries if we use Index Match or Vlookup it shows the first match but the filter formula shows a #REF error if there is no space to add second result. In this case, we get to know, we have duplicate entries. We might need to add multiple conditions to get more accurate data. If we get to know there is one unnecessary entry we can delete it or make necessary changes in the document.
It works as duplicate detection.
- Works with Named Range
It’s another way to make your formula shorter. Named ranges are easy to remember and make the formulas easy to understand.
- Works with array range {}
For Vlookup, data has to be in order, we can’t get the left column value through the left match and because of this limitation, I prefer Filter over Vlookup. With curly brackets, you can create custom array or range so you don’t need to move columns here and there.
- Nesting with other formulas
It works with Countif, Sumif, Unique, and other functions and formulas.
After filtering out your data you can also sum count and nest it with other formulas to get the desired result.
- Compatible with ImportRange
If you have connected multiple spreadsheets with ImportRange function, you can filter out data with conditions.
Conclusion
Filter function is very useful function when it comes to filtering out the information based on conditions. It’s like using Filter feature, it’s better than other lookup function because it’s easy to understand, works with other functions, and simple to implement.
It’s best alternative for Vlookup, Index Match, Hlookup and Xlookup. In most cases it would be better than other lookup functions.
The only drawback I see in the function is, we cannot apply it to range with Array formula.
