Multiple Value Lookup to return an array to then be used in another formula

[TheFrog]

Hi Guys,

I have a scenario where I know the combination of hard drives that is most economical to use for a given system design. I know the Total Storage, the Quantity of HDD's used, and the capacity of each HDD used to create the total storage.

In addition to this I have a data rate for information flowing to the HDD's that my desired target device must be able to support (in Mbps), and the number of sensors that it must be able to support.

I have entered the relevant data for the devices (or actually classes of devices) so that I can filter on these values to see which are my suitable options.

The data is on its own worksheet and looks like this:

[TABLE="width: 463"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]Type[/TD]
[TD]Mbps[/TD]
[TD]Channels[/TD]
[TD]HDDs[/TD]
[TD]MaxHddTB[/TD]
[TD]Storage Capacity[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]96[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]96[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD="align: right"]512[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]144
[/TD]
[/TR]
</tbody>[/TABLE]

What I am doing at the moment is filtering by each column in turn to leave me with a list of suitable device types / classes, and this works well.

My goal is to produce the list of suitable device types as an array (or possibly a list that can be turned into an array) to then start using that in the selection of a product from one of the classes / types that re suited to the job.

So in summary I know the values I have to meet for the last five columns of the lookup, and need to return the first column from the table for all types / classes that would be suitable.

An example would be I have a system needing 110.06Mbps, total storage of 8TB, built from 2 x 4TB HDD's and it has 45 sensors attached to it.
I set the filters as follows:
- # Channels = Filter by numbers >=45
- # HDD's = Filter by numbers >= 2
- Max HDD Size (this is the size of an individual drive) = Filter by numbers >= 4
- Storage Capacity = Filter by numbers >= 8
- Mbps = Filter by numbers >= 110.06

This will give me a resultant set of data that looks like this:
[TABLE="width: 463"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]Type[/TD]
[TD]Mbps[/TD]
[TD]Channels[/TD]
[TD]HDDs[/TD]
[TD]MaxHddTB[/TD]
[TD]Storage Capacity[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]96[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]96[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD="align: right"]512[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]144[/TD]
[/TR]
</tbody>[/TABLE]

So my suitable classes / types are 23, 24, 25, 26, and lucky number 34!

Is there a way that I can generate this list of types / classes as a formula so that I can use it in the next step of my calculations?

I am hoping to avoid using any VBA in this. I am quite comfortable with VBA but I cannot guarantee that others who might use this are. If it can be done with a formula I would also be able to use it on my phone's Excel which would make it highly portable.

Penny for your thoughts?

Cheers

The Frog

 

[Eric W]

You could set up a table of your filters, and have a formula automatically create the matching list, like so:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
Type	Mbps	Channels	HDDs	MaxHddTB	Storage Capacity	Mbps	Channels	HDDs	MaxHddTB	Storage Capacity
Min
Max
Type	Mbps	Channels	HDDs	MaxHddTB	Storage Capacity

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]100.06[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]512[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]512[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet19​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J7[/TH]
[TD="align: left"]=IF($I7="","",INDEX(B$2:B$35,MATCH($I7,$A$2:$A$35,0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I7[/TH]
[TD="align: left"]{=IF(I6="","",IFERROR(INDEX(A:A,SMALL(IF(($B$2:$B$35>=$J$2)*($B$2:$B$35<=$J$3)*($C$2:$C$35>=$K$2)*($C$2:$C$35<=$K$3)*($D$2:$D$35>=$L$2)*($D$2:$D$35<=$L$3)*($E$2:$E$35>=$M$2)*($E$2:$E$35<=$M$3)*($F$2:$F$35>=$N$2)*($F$2:$F$35<=$N$3),ROW($B$2:$B$35)),ROWS($I$7:$I7))),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The formula in I7 is an array formula, confirm with Control+Shift+Enter. Then copy it down the column as far as needed. The formula in J7 is just a simple lookup, insert it then copy it down and to the right as far as needed.

Let me know if this is what you're looking for.

 

[TheFrog]

Hi Eric,

I see where you are going with this. I will have a chance to test this over the weekend and I'll come back to you. This is looking promising.

I will attempt to just get the list of types that could suit, not worried about bringing back the other data.

As a passing thought what about using something like goal seek to achieve a result? This wouldnt be on the mobile versions but it would allow for even more complicated calculations to be done, possibly including the next step of selecting the most economic model from the classes.

Do you know of a good resource on using goal seek to get my knowledge on this up to speed? Its one area of Excel I have never played with.

Cheers

The Frog

 

[Eric W]

Hi TF,

Using Goal Seek, or its big brother Solver, is certainly an option. It really depends on how complicated your situation is. If you just want to look at the set of matching configurations, and the disks are at a fixed price, then you could add another column, # of disks times price, then look for the minimum value in that column and you're done. If you're looking at "I can use 4 disks at $100 each, or 2 disks at $189 each" then you're looking at using Solver. If you want to add performance or mean time to failure, then even more so. In essence you'd set up a set of equations on your sheet, set up a single cell formula that would define the "best" configuration, then let Solver run through all the possibilities until that cell is maximized. (Or minimized if you're looking at price.)

I don't have any particular resources to recommend on how to use it. You can Google "Excel Solver" for some basic tutorials. You can search this forum to see how it has been used to solve particular problems that might be close to yours. If you get the problem well-defined, you can come back and ask for help here.

Let me know what you come up with.