Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-26-2014, 11:31 PM
tinfanide tinfanide is offline Excel Formula: return a range of cells that match Windows 7 64bit Excel Formula: return a range of cells that match Office 2010 32bit
Expert
Excel Formula: return a range of cells that match
 
Join Date: Aug 2011
Posts: 312
tinfanide is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 08-27-2014, 09:20 PM
Catalin.B Catalin.B is offline Excel Formula: return a range of cells that match Windows Vista Excel Formula: return a range of cells that match Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 08-28-2014, 05:30 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Excel Formula: return a range of cells that match Windows 7 64bit Excel Formula: return a range of cells that match Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #4  
Old 08-29-2014, 07:38 PM
tinfanide tinfanide is offline Excel Formula: return a range of cells that match Windows 7 64bit Excel Formula: return a range of cells that match Office 2010 32bit
Expert
Excel Formula: return a range of cells that match
 
Join Date: Aug 2011
Posts: 312
tinfanide is on a distinguished road
Default

Thank you for the array formula and detailed explanation.
It has answered my doubt on how to deal with ranges of cells.
Reply With Quote
  #5  
Old 08-30-2014, 07:03 AM
Catalin.B Catalin.B is offline Excel Formula: return a range of cells that match Windows Vista Excel Formula: return a range of cells that match Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

You're wellcome

Catalin
Reply With Quote
Reply

Thread Tools
Display Modes


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
Excel Formula: return a range of cells that match 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
Excel Formula: return a range of cells that match Moving formula range multiple cells when moving sum over one cell FraserKitchell Excel 4 02-26-2010 10:38 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:07 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft