Excel Range with IF

Question

Is there a function that will create a range (from a range) if they match values? Essentially, I'm looking for something like COUNTIF, that will return the cells that actually match my IF.

Ideally, something like RANGEIF(<NORMAL_RANGE_HERE>, ">"&C12), which will return all cells in <NORMAL_RANGE_HERE> that are greater than C12.

Answer 1

The solution here is to use IF, but use it as an array function. For example, if you have this table (sorry for the formatting):

    A   B   C   D
   ______________
1 | 1   3   2   5 
2 | 8   1   3   2
3 | 5   4   3   9

Now say you only wanted values that were greater than three in an identical table.

1. Select an empty block of cells matching the size of your original table.
2. Now type in the formula (remember to make sure you have your whole new cell block selected, very important): =IF(A1:D3>3,A1:D3,"").
3. Now don't just hit Enter... In order to enter this as an array function you need to hit Ctrl-Shift-Enter.
4. Now that one formula is applied to the entire cell block as an 'array formula' and it will evaluate the range you put into the IF formula cell by cell depending on the cell's location in the array. You can tell it was applied as an array formula by clicking on one of the cells. In the formula editor you should see the formula enclosed in curly braces like this: {=IF(A1:D3>3A1:D3,"")}

You should end up with (assuming your empty block was F1:I3):

    F   G   H   I
   ______________
1 |             5 
2 | 8          
3 | 5   4       9

Hopefully this is enough to get you going. Do a Google search for "excel array formula" for more info. Hope this helps!