Advanced Index/Match Functions

Hi everyone,

So I have created this advanced spreadsheet for the nursery I am working for. This is part of a market analysis for the business plant I am writing.

I have a list of over 1000 plants in the A column, followed by 8 nurseries (including the one I am working for) in Columns D to K. Each of those columns has a unique fill color and an X in the cell, with the text colored the same as the fill color. This X I am using as a cell reference for the plant name and the nursery it's available from to make the other formulas I have running.

I already have some cool formulas running showing the tally of plants and market percent for entire nurseries, plant types, and even plant genus. These statistics I will be using to write in the business plan to show our current and potential markets.

What I am trying to do now is develop a formula that will list only the plants that are found in all of the 8 nurseries (e.g. Themeda australis, which all nurseries grow). I believe that I should be using a combination of Index and Match functions, but I cannot seem to get the right combination going to make it work.

I would be greatly appreciative of any help.

Many thanks in advance,

Richard.

Thanks for that! That gives me much to work on. I can see it coming back that only 4 plants from ~1000 are shared by all nurseries. I can see that I can also use this to find other plants that are shared by 7, 6 or any other combination of nurseries.

Is there a way I can generate the same response, but by using a formula? I am trying to learn more about writing Excel formulas. I think that it should go something like (written in Column M):

=INDEX(A5:A1090,MATCH(8,L5:L1090))

Where A is the column with plant names, and L is where the CountA results are, with 8 being the count of X's.

This, however, is not generating a response that I want, responding with the last plant on the list that is found in 1 nursery only.

Thanks for the reply. It seems as if I have much to learn.

Slicers, hey? I just Googled that term and it's come up with a whole host of functions from Office 2016 that I had not been aware of. It has given me much to digest.

Again, thank you for your replies.

Hi,
You can use this array formula (introduce with Ctrl+Shift+Enter)

Formula:

Please Login or Register  to view this content.

see attachament

Wow, dude. That formula is pretty sublime. And I can see how it works from the attachment you've provided. I can see that the end of the formula you've put the ROW(A1) without the absolute cell reference, ensuring that when you copy it down it will find the next result from the array. I believe that the central part ROW($A$1:$A$8) lists the array formula for the first 8 rows of column 8.

There are some functions here that go over my head.The MMULT function is new to me. After Googling it I can see that it finds the "matrix product of two arrays".

But why put the "--" after that? I am not familiar with that function or how it makes the formula work.

Also, when I try to adapt this into my own workbook, it doesn't seem to work...

I'm going to have to study this some more.

Again, thanks for the advice.

--({TRUE,FALSE,TRUE})►{1,0,1}

for easy adapt define 2 names:
1. "plants" with

Formula:

Please Login or Register  to view this content.

and
2. "nursNr" with

Formula:

Please Login or Register  to view this content.

This 2 formulas must be adapted for your workbook. See my atachament.

For filtered list put this formula and drag down and if it`s necessary, now can drag to right

Formula:

Please Login or Register  to view this content.

Change ";" with ","