I’m assuming here you already know how vlookup() function works. Yes, nesting index() and match() is better, but for the beginners out there, just figuring out vlookup() will be HUGE. So if you never managed to get a vlookup() function to work, stop reading this and go research that instead.

Here’s a useful image I found on Sheetgo blog that illustrates the possible results. I find the use case illustrated in D5 field especially interesting, as knowing this formula when I was working at Sellfy would make it possible for me to work without bothering the devs on more than 1 campaign.

Now, some practical example that uses indirect() function 👇

## 1 Sum everything in column ABOVE this cell

Super useful for when you need to calculate list of, for example, expenses — that just keep piling up. And then you add a row at the very top of the list. Without this, the sum() function will annoyingly not “expand” to include the new row. So use this instead.

An example of how adding 1 row at the top of the list breaks the sum() function. Worst case is when you don’t know this is happening.

## 2 Standardisation of URL (no www, https, trailing slash)

Pretty useful I would say, when you scraped a list of URLs and need them all to be in the same style.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(RIGHT(A2,1)=“/“,LEFT(A2,LEN(A2)-1),A2),”https://“,,1),”http://“,,1),”www.”,,1)

## 3 Get domain name from URL

So the problem with this formula is that it doesn’t filter out subdomains and it’s kinda hard to make these kind of formulas play nice with both subdomains and TLDs like .co.uk etc.

=lower(trim(REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(C2,”https?://“,””),”^(w{3}\.)?”,””)&”/“,”([^/?]+)”)))

And so with this formula, if you have a huge list with URLs and, for example, there’s all kinds of versions of wikipedia inside, instead of getting one clean domain name wikipedia.org, you will end up with multiple subdomains like en.wikipedia.org, de.wikipedia.org, es.wikipedia.org, it.wikipedia.org etc.

If that’s what you need - great. If not, the solution to this is using a script as described in this StackOverflow answer. The added bonus here is that websites hosted on .blogspot.com and some others are treated as TLDs and won’t show simply as blogspot.com in your list.

## 4 Get unique items from multiple columns

Pretty much does what is says. If you have multiple columns of similar data and want to find only unique values - this is the formula to do it.

=unique(query({C3:C;D3:D;E3:E}, “where Col1 <>’’”))

This formula gets quite messy if you have a lot of columns, for example,  here’s another version for 160 columns.

There must be a cleaner way to do this with built-in functions without running into 50k character limit. If you know it - my DMs are open.

## 5 Custom formula in filter view

I’m only including this here because I often forget how to do this myself. This has nothing to do with filter() function. And everything to do with this “Create a filter” button that gives you a custom filter view.

Inside this filter view, instead of manually selecting each column you want to filter and set the value (like DR > 25, organic traffic > 1000) you can actually just select “Filter by condition” >> “Custom formula is” and write down your formula.

# Did you enjoy this?

Leave your email and I'll let you know when new articles are published. (not really, this form is not connected to anything atm. But I'll get a notification that you "subscribed" 😅)