Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
Just to clarify. If your Range was A1:Z1000 how would you imagine the results if you had some in multiple columns? Or non-contiguous cells? How would you expect to display results? <NORMAL_RANGE_HERE> could be anything like (A1,B3,C5:200,D3:E50,M1:Q900) Not sure how you expect to display the result. How about a macro? – datatoo Mar 13 '11 at 0:31
I think Excel might NOT have this kind of function. Maybe you can tell us why you want that, so that we can suggest some alternative solution if possible. – wilson Mar 15 '11 at 8:57

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!

share|improve this answer
Aaaaaa ... this Ctrl-Shift-Enter is an interesting trick! @pnuts well yes, green tick would be nice, but score is not everything. You learn a lot by answering -- so answering is a reward by itself. – George Jul 1 '15 at 7:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .