obtaining row & column of SMALL() selections

[mendowill]

Thank you to all you excel gurus who respond to these questions, and to anyone who may take a crack at this one:

Big picture:

I have a spreadsheet that has created a list of possible solar system configurations, and listed them by kW rating. essentially i have an array which will only display kW ratings if the system is within several constraints that the user chooses. what i need to do is move these active kW ratings of system configurations to a column and extract several cells of information for each possible system configuration.

I have sorted the info by kW easily:

=SMALL($E$5:$O$136,A5)

with this small() formula i can arrange all possible kW ratings in ascending order. what I can't do is extract the information that goes with each rating. I have tried match():

=MATCH(Q5,$E$5:$O$136,0)

which should compare the value chosen by small() in Q5 and find it in the array. but instead i only get a #REF! error. I've also tried:

=INDEX($B$5:$B$136,ROW(SMALL($E$5:$O$136,$A5)),1)

which i thought would easily return the data from column B based on the row of selected kW rating in small(). wrong again, b/c small() returns a value (kW rating), and not a cell reference..

Does anyone have a suggestion? Maybe someone sees a better "Big Picture" approach?.. I'm not well experienced with VBA, but if you think it would be an efficient way to accomplish this task then please share and I will learn it.

Thanks so much in advance.

 

[Jonmo1]

Welcome to the board...

Addressing the "Bigger picture" as you put it..
What is the purpose for wanting the row and column for the smallest value?
Do you plan to use that to build an address to be used in another formula?
If so, there is likely a better way..

Please describe what you intend to do with the row and column #s once you have them.


Now addressing the actual qustion, yes it's possible.
However, is it possible for there to be duplicates(or triplicate or more) of the smallest value?
Say if the smallest value is 10, is it possible for there to be 2 or 3 or more 10's in the range?
If so, which one do you want to use?

 

[mendowill]

thank you for your quick response. yes, I will be using the column and row to build an address in another formula. I hoped to use index() to find the module in col B, inverter in col C, and # of modules in row 5, all corresponding to the particular cell that matched the user's criteria and eventually was sorted by small(). if you have a better suggestion then I am all ears.

=INDEX($B$5:$B$136,ROW(SMALL($E$5:$O$136,$A5)),1)

It is possible for there to be duplicate values in the range, and I would like to use both. I would like the different module/inverter configurations for each duplicate value to display alongside the value. For example, a 4.56 kW option arises twice with given user inputs, and I would like to be able to display the Kyocera 235/SMA5000 option as well as the Kyocera 235P/SMA5000 option.

 

[Jonmo1]

That is going to be quite difficult with formulas.
Especially considering the possibilty of duplicates.

Likely needs to be VBA...

What exactly do you want to happen when it finds the smallest value(and any dupes).
Find the corresponding value from the same row in Column B
And Put it Where?
What if there are Dupes, where should the subsequent values go?

 

[mendowill]

hmm, well thanks for your help, i'll post if i figure something out.

 

[Jonmo1]

hmm, well thanks for your help, i'll post if i figure something out.

posts crossed...lol
Reread my last post, I added more..

 

[mendowill]

I'm trying to generate a list of these possible system configs. So once my array generates several options, lets say 4.56 in G14, 4.56 in H15, and 5.23 in H16, I would like to create a table with columns: kW rating, # modules, module, inverter. the first three rows would be:

kW Rating # Modules Module Inverter
4.56 kW 24 Kyocera 210 SMA SB4000
4.56 kW 24 Kyocera 210P SMA SB4000
5.23 kW 26 Sanyo 210 SMA SB5000