How to combine Filter and IF-Statements in Google Spreadsheet

google-sheets filterfunction
FlorianT. picture FlorianT. · Apr 19, 2016 · Viewed 7.6k times · Source

Problem I have two sheets.

  • Sheet 1: Is supposed to Contain my Dashboard.
  • Sheet 2: Contains a table with three columns, starting at row 16

So what I want to do is

  1. type in a String in 'Sheet 1'!$B$2, have the formular look for that value in Sheet 2, and return all three columns.
  2. If 'Sheet 1'!$B$2 is empty, I would like to show all columns and rows of Sheet

Here is what I tried: =IF(isblank($B$2), 'Sheet 2'!A16, FILTER('Sheet 2'!A16:A$1000, 'Sheet 2'!$C16:$C$1000=$B$1)) This works does the trick for Nr. 1, but not for Nr. 2. It only shows the first row obviously.

If I pull the formular down the rows, Nr. 2 works, but Nr. 1 does not. The error it gives me is REF - Array Result was not expanded, because it would override data in F16. F16 is the column, the formular is in.

Here is a sample sheet.

Can anyone advice?

Answer

Max Makhrov picture Max Makhrov · Apr 20, 2016

For your case formula will look like this:

=IF(isblank($B$1), ARRAYFORMULA(Sheet2!A16:D1000), 
FILTER(Sheet2!A16:A$1000, Sheet2!$C16:$C$1000=$B$1))

Related questions

How to highlight cell if value duplicate in same column for google spreadsheet?

I am looking for formula for google spreadsheet highlight cell if value duplicate in same column can anyone please assist me for this query?

Read More
Conditional formatting based on another cell's value

I'm using Google Sheets for a daily dashboard. What I need is to change the background color of cell B5 based on the value of another cell - C5. If C5 is greater than 80% then the background color is green …

Read More
How to get the correct range to set the value to a cell?

I want to set text or number in Google Sheet from script. I want to set Hello or number 9 in cell F2. I found this code so far: SpreadsheetApp.getActiveRange().setValue('hello'); but that doesn't specify which cell.

Read More