Help To Reverse Lookup Formula Extraction Order

Hello Everyone,

My workbook is attached below. I have a helper table with 5 helper columns. The columns contain lookup formulas that extract all matches from bottom to top of a separate data table. They're also dependent on selections from drop downs, therefore the lists in these columns are dynamic and change.

What I need are formulas that extract all the matches, but from the other way around, from top to bottom.

For example, if the list in the Type column was currently extracted in the order Harvest > Intermediate > End, I would actually like it to be in the reverse order: End > Intermediate > Harvest.
Or depending on the drop down selections, the list might only have these values in the order: Intermediate > End, which I would actually like to be in the reverse order End > Intermediate.

Here is an example of the formula in the first row of the column Type of the helper table.

Please Login or Register  to view this content.

Unfortunately, I could not figure it out on my own, so I greatly appreciate anyone who looks or attempts to help me with this.

Thank you all!

*EDIT: I attached the wrong workbook originally, I am so sorry

Did you attach the correct file? I can't see a lookup anywhere!!!

Much better!! Before I get stuck into this, there is one VERY important question. On sheet Form, you have one row containing a series of DD boxes. In your real sheet, how many such rows are there? With your current approach, you will need to replicate that entire table for EACH row you need DD's on. That may require a complete change of direction, taking you closer towards the sample sheet that you posted initially. FWIW, I am familiar with the sort of sheet in your initial sample sheet. I've set it up for a quaternary level DD, but not yet for a quinary level DD. It can be done... Let me know ASAP. I have a few hours hanging around in airports on Sun & Mon. It'd help pass the time!!!

Is this what you had in mind??

in Helper, B2:
=IFERROR(INDEX(ProductsT[Brand],MATCH(0,INDEX(COUNTIF($B$1:$B1,ProductsT[Brand]),0),0)),"")

in Helper, C2, copied across and down:
=IFERROR(INDEX(ProductsT[Type],MATCH(1,INDEX((COUNTIF(C$1:C1,ProductsT[Type])=0)*(ProductsT[[Brand]:[Brand]]=Form!$B$3),0),0)),"")

They're non-array formulae.

I have to catch a couple of flights now. I'm back in 6 or 7 hours. It looks like you have something clever going on there in your Named Ranges. I'll take a closer look when I reach my hotel.

I can't remember this at all!! A lot has happened to me since then, too. Including being locked into Barcelona airport for 5 hours while a full-scale riot was going on outside the terminal building.

All OK now! I'll look at this tomorrow, as I'm leaving now for the rest of the evening.

OK. Here it is again, fixed, I think. Try it out.