I have a collection of plants of which various characteristics have been listed in a worksheet against each named plant.

I wish to prepare a list of those plants displaying a certain characteristic(s) eg minimum temperature requirement.

An internet search suggested an array formula using terms with which I am unfamiliar, and which produced an error as displayed in the attached spreadsheet, which I hope gives some idea of what I'm trying to achieve.

Can anyone help, please?

It could be argued that a database would be better, but I've never made one from scratch, and online searches haven't come up with anything simple enough for an oldie like me to fathom, I'm afraid - all the examples I've looked at don't explain the absolute basics. It would, however, be nice for me to link my own photographs with the named specimen somehow. Ideas, anyone, please?

Any comments gratefully received.

Attached Files

Plants.xlsx (8.8 KB, 10 views)

Are you still using XL 2007 ?

You could also use a simple Pivot Table, and filter the row labels as required, no complicated formulas needed
In XL 365 the FILTER function is also available
And your formula should be committed with Ctrl+Shift+Enter and pulled down

Attached Files

Copy of Plants.xlsx (12.6 KB, 11 views)

OK Thanks Pecoflyer, and I'm using xl 2013.


BUT when I copied and tried to paste in your formula, it returned an error as shown in attached, when I did Ctrl,Shift,del

Attached Files

New formula.docx (170.8 KB, 8 views)

Please post your Excel sheet. As you can see it works in the sheet I attached

Is there some real reason you can't simply use a filter?

Add header row to your table, e.g. Plant, MinTemp;
Activate any single cell in table, and select from menu an Filter option (in my version of Excel it is Home.SortAndFilter.Filter) - you get selection options at column headers;
For 'Min temp' column, click on filter option, select 'Number Filters', then 'Greater than', and type 5 into condition field. Click OK, and you get what you wanted! You didn't like the result - change the filter condition in any way you like.

Sorry for delay in responding.
The excel sheet you worked on is the one I've been using your formula in, and I still get the same result as previously reported, despite several attempts and careful checking.



Puzzling indeed!

[QUOTE=ArviLaanemets;163701]Is there some real reason you can't simply use a filter?

Thanks for your input.


I'm not familiar with the Filter option, but have briefly looked into it for Excel 2013 (which seems different from later versions from what I've seen so far) but appears to suit my purpose. I will look into it further and report back when I can.

My bad try committed as an array formula with CSE

[QUOTE=silverman166;163725]


That works beautifully - and so easy when you know how!
Thankyou!

OK that works better, but only returns one result even when there are more instances in the list, so not quite what I'm after.


I very much appreciate your time, but would not wish you to spend more time on it, unless you really want to, as the Filter option you also suggested, and is detailed in another post, works OK for me.


Many Thanks!

As you can see in the attached, this formula works perfectly.Did you commit with CSE?

Attached Files

Copy1 of Plants.xlsx (13.2 KB, 6 views)

Thanks for replying to my last post, and Yes that works fine..........BUT it is not the same formula you sent me in the last post! This one starts with IFERROR... whereas the previous one didn't, but thanks to your persistence I now have the choice of two methods!



I am pleased with that result and think maybe I'll go with this one.


And the bonus is that I've learned quite a bit, too!


So thanks again.

Further to your last post, and my reply, I've just found that when I post the latest formula into my original spreadsheet, it still does not work as shown in the sample spreadsheet posted, which appears to show a Pivot table, the concept of which I'm not familiar.


I've attached it to demonstrate.


Very confused now!

1. The IFERROR was added to avoid errors when while pulling down, there are no more results satisfying your requirements
2 Your original file has a value of "<5" in D3. This is a text string and will not work as such when comparing to numbers from col B. So you have to replace it with a single value, not using a text string

3 The Pivot Table was another example I provided for a possible solution. Although it is not necessary for a small table, it would be helpful for larger tables.

4. The solution provided is an array formula. They tend to slow down sheets with large ranges and most are nowadays replaced with built-in functions in newer XL versions like O365

Attached Files

Copy of Plants_1.xlsx (10.2 KB, 7 views)