Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
1  
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

1 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!

share|improve this answer
    
Perfect answer, I'd say - shame no green tick! –  pnuts Sep 27 '12 at 19:49

Your Answer

 
discard

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.