Array Functions

Trying to figure out a formula in order to accommodate the following:

I have a table in which there are a bunch of amplifiers and a bunch of different types of speakers. each amplifiers has enough power to support a certain number of speakers depending on the brand and power size. I need a function that will be able to let me select a type and quantity of speaker, and output the amplifier needing to be used.

For example.

---------Amp1-------Amp2-----Amp3------Amp4
Sp1------1-3---------4-6--------7-9--------10-12
Sp2------1-4---------5-7--------8-10-------11-14 <<<---Number of speakers that will fit each amp by column
Sp3------1-2---------3-5--------6-8---------9-12

In this model I want to select the speaker type, and the quantity of the speaker, and have a cell tell me which amplifier to use.
My major problem is that I cant figure out a way to combine all of the Fx's to provide a Row/Column/ and In between statement that will achieve what I need.

Please Help, Thank you!

See attached. With speaker quantity in C7 and speaker type in C8, and data in B2:E4

In C9

=INDEX($B$1:$E$1, MATCH(C7, INDEX($B$2:$E$4, MATCH(C8, Speaker,0),)))

This is SO close, appreciate the help, but when I plugged this into my version using the upper limit of each amplifier, it selects the amp above it. This is why i need the in between function I think anyway.

using the example above

---------Amp1-------Amp2-----Amp3------Amp4
Sp1------1-3---------4-6--------7-9--------10-12
Sp2------1-4---------5-7--------8-10-------11-14 <<<---Number of speakers that will fit each amp by column
Sp3------1-2---------3-5--------6-8---------9-12

say I select speaker 2 and i have 6 of them. I should get and answer of amp2

However, with the formula you've provided, even though I set it up the same exact way, it will tell me that with 6 speaker 2's I should use amp 1.

I think that this is because it is set to the upper limit of the amp instead of looking at lower and upper limits. which is why I initially though that I needed an in between function of sorts. I should have mentioned that the upper limit of the amplifier is the MOST amount of speakers allowed on that amp. Meaning that I cant have more than 7 Speaker 2's on amp2.

Since you do have an upper limit, I added another column with the header "Too Many Speakers" Putting one more than your maximum for each speaker in that column, just expand the range for your data.

No, What formula are you using?

If I see your worksheet, I can see your error. (Go Advanced> Manage Attachments)

Heading out for the night, be back tomorrow

The numbers in your tables need to represent minimums. More specifically, they should be 1 more than the maximum of the previous amp. So for Sp1, AMP1 =1, AMP2 = 14, etc.

I see AMP 6 and 7 have the same max for Speaker 1. My formula will return the higher amp. If you want to be able to return multiple values, we can come up with a more complex formula. Is that preferrible?

Okay, in this sheet, I put min and max speakers for each amp in separate cells (i.e. Sp1/amp 1 can have 1 to 13, Amp 2 can have 8 to 17, etc.

In C13, is this ARRAYED formula copied to the right

=IFERROR(INDEX($B$1:$N$1, SMALL(IF(MOD(COLUMN($B$2:$N$2),2)=0, IF(INDEX($B$2:$N$10, MATCH($A$13, Speaker,0),)<=$B$13, IF(INDEX($C$2:$O$10, MATCH($A$13, Speaker,0),)>=$B$13, COLUMN($B$2:$N$2)-1))),COLUMNS($A$1:A$1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
So, with my table, Sp1 and Qty of 20 returns AMP3,4 and 5

Glad to help. Thanks for the rep.