Forum Discussion

johannsomosa's avatar
johannsomosa
Copper Contributor
Dec 16, 2024

Store a range of numbers in one cell? Excel Question

Hey Microsoft community, I'm working on a project and I'm wondering if this task I'm requesting for Excel to do is possible. If so, is it suitable I continue with Excel or with a different program...
BI & Data Analysis
excel
Excel on Mac
Formulas and Functions
johannsomosa's avatar
johannsomosa
Copper Contributor
Dec 17, 2024

"Sheet 1" lists Hardiness Zone, Crop, Annual or Perennial, Min. Temperature Range, Max Temperature Range. My goal is to pull up all the crops that exist within the Hardiness Zones. (Ex. If searching Hardiness Zone 9, every Crop with the Hardiness Zone of 9 is pulled up.) 

I'm trying to figure out how store data in the manner that if I wanted every crop with Hardiness Zone of 9, it would pull all the crops. Originally, my idea was to store the hardiness zones as a range. However, I haven't found a solution towards that.

HansVogelaar's avatar
HansVogelaar
MVP
Dec 17, 2024

I'm too lazy to recreate that. Please attach a small sample workbook (not screenshots) demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar.

  • johannsomosa's avatar
    johannsomosa
    Copper Contributor
    Dec 18, 2024

    Hey HansVogelaar , 

    Excuse me, of course let me share a drive file. https://docs.google.com/spreadsheets/d/1shn8arKSoY9nkuGF7cDEFMiy8rnOWc4d/edit?usp=sharing&ouid=113669357501570955749&rtpof=true&sd=true

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Dec 19, 2024

      Based on the formula proposed by m_tarler :

      =TEXTJOIN(", ", TRUE, FILTER(USDA_Crops5[Crop], ISNUMBER(SEARCH(", "&[@[Hrdiness Zone 2023]]&",", ", "&USDA_Crops5[Hardiness Zones]&",")), "None"))

Resources