Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
Perfect answer, I'd say - shame no green tick! – pnuts Sep 27 '12 at 19:49
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
@SqLPolice "You learn a lot by answering" - in my case almost everything I know about Excel! But it would be nice if a user still around (last seen 1 hour ago) bothered to acknowledge a good answer to his question. I would give ictatha two more privileges and should help others to recognise quality. – pnuts Jul 1 '15 at 18:19

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.