#1
|
|||
|
|||
Excel Formula: return a range of cells that match
A B
cat 10 cat 20 dog 30 cat 40 dog 50 cat 60 How can I return the range of cells that match "cat"? The assumed answer: A1:A2, A4, A6 Thank you! |
#2
|
|||
|
|||
Hi,
A formula like this: =IF(A1:A6="cat",ADDRESS(ROW(A1:A6),COLUMN(A1:A6)),0) will return: {"$A$1","$A$2",0,"$A$4",0,"$A$6"} You can replace the red 0 with "" if you need this. If you use the formula like this: =IF(A1:A6="cat",A1:A6,0) it will return a range of those values: {"cat","cat",0,"cat",0,"cat"} Or, you can return a corresponding set of values from the next column: =IF(A1:A6="cat",B1:B6,0) returns: {10,20,0,40,0,60} Note: use them as CSE formulas. Cheers, Catalin Bombea |
#3
|
||||
|
||||
Perhaps use a filter
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
Thank you for the array formula and detailed explanation.
It has answered my doubt on how to deal with ranges of cells. |
#5
|
|||
|
|||
You're wellcome
Catalin |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copying text range of cells to different cells adds an extra line | jpb103 | Word VBA | 2 | 07-23-2014 12:22 PM |
Excel Formula - Conditional replacement of cells | mag | Excel | 0 | 10-27-2012 08:30 PM |
Sum Formula in the range with Numeric and NonNumeric data cells | Spanec | Excel | 2 | 01-12-2012 09:15 AM |
Count range cells eliminating merge cells | danbenedek | Excel | 0 | 06-15-2010 12:40 AM |
Moving formula range multiple cells when moving sum over one cell | FraserKitchell | Excel | 4 | 02-26-2010 10:38 AM |