Index function with different arrays based on cell reference

[SparkyTheElectrician]

Index Match with Input Based Array.xlsx

It's me again.

I am trying to not go through the hassle of writing an If function with in my case 30 different cases and defining 30 different arrays.

The attached spreadsheet outlines what I am looking for.


In a nutshell, when using an Index function, the first section to populate is the array. In my case the array is based on the insulation temperature of the cable. I would like to pick a temperature and then assign the appropriate array.

Then I have a second question in the spreadsheet. I do not know how to outline it in detail. If you could look at it in the attached spreadsheet. Looking to increase values until a function is true.

[windknife]

I guess about Q1.

B5
=INDEX(FILTER(Data!A4:E23,Data!A4:A23=B4),B3)

[AliGW]

Could you please go back and mark your previous threads as solved.

Choose Thread Tools from the menu link above and mark them as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[SparkyTheElectrician]

Could you please go back and mark your previous threads as solved.

Choose Thread Tools from the menu link above and mark them as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

I am sorry, I forgot. I marked the other one as solved and pushed the rep up.

[TMS]

Another way for Q1:

=INDEX(INDEX(Data!A4:E23,0,MATCH(B2,Data!A2:E2,0)),B4)

[SparkyTheElectrician]

Index Match with Input Based Array.xlsx

I revised the text for the 2nd question I had.
I could simply look up the next higher gauge and run a check. in 99% of the cases that will work. However, I am in general looking for a formula / function that executes in a loop (incrementing the variable by 1 for each loop) until a criteria is met.

[windknife]

Try this in O6.

=IF(O4>=B7,"",XLOOKUP(B7,Data!I3:I6,Data!G3:G6,"",1))

[AliGW]

This one is still not marked as SOLVED:

https://www.excelforum.com/excel-for...le-exists.html

[SparkyTheElectrician]

This one is still not marked as SOLVED:

https://www.excelforum.com/excel-for...le-exists.html

I am really not good at organizational skills or keeping track of things. I marked it as solved now. I hope that works.

[SparkyTheElectrician]

IF(O4>=B7,"",XLOOKUP(B7,Data!I3:I6,Data!G3:G6,"",1))

Thank you windknife.

Please correct my interpretation if I am wrong.

Look for B7's value in array I3 through I6 and return matching data found in G3 through G6. If none found return an empty space "" but then it also returns the next larger item if none found.

How is the syntax , "",1) processed in Excel?

[mallsanta]

I understand your concern about avoiding multiple If functions and defining numerous arrays. For your first question about dynamically selecting the array based on the insulation temperature, you can use the INDEX-MATCH combination. Check out the attached spreadsheet for a sample implementation.

As for your second question about incrementing values until a certain condition is met, I'll take a closer look at the spreadsheet to provide a more detailed response.

[AliGW]

Thanks.

[SparkyTheElectrician]

My browser crashed so I am rewriting what I put down already once.
What is a good hands-on resource to learn Excel formulas? I have another question but I am staring to feel embarrassed. Wanted to give it a try myself.

[DJunqueira]

For cell Calculation!B3 you could use one of the following formulas:

=SWITCH(B2,"","","60 C",2,"75 C",3,"85 C",4,"90 C",5)

=LOOKUP(B2,Temp,COLUMN(Temp))

And in Calculation!O5

=IF(O4>=B7,O2,"Increase Size")

[TMS]

Thanks for the rep.

[TMS]

Well, you probably need to narrow the requirement a bit as there are hundreds of functions.

See: https://bettersolutions.com/excel/fu...-functions.htm

And: https://bettersolutions.com/excel/fu...plete-list.htm

If I need reminding of the syntax of a function, I usually just Google it. There are lots of sites that provide worked examples (and, of course, Microsoft’s own pages).

Excel itself also prompts you for the parameters to a function . . . but you do need to know what you want to do.

[SparkyTheElectrician]

I mostly consolidate engineering tables. Meaning, I need anything that can look up arrays, rows, columns, solve equations by increasing specific values by 1 until the equation is true. (if that is even possible).

Functions that can create tables. For example, instead of installing one big cable, you can install (2) cable of type A, or 3 of type B or 4 of Type C

[DJunqueira]

I mostly consolidate engineering tables. Meaning, I need anything that can look up arrays, rows, columns, solve equations by increasing specific values by 1 until the equation is true. (if that is even possible).

For that learn about Solver in Excel.

Click here for a vide about Solver.

[SparkyTheElectrician]

I tried it myself and got half solved but I believe, I am overthinking the other half.

I attached a spreadsheet. In the example shown the letter found by the formula should be C which is 75A

Tried multiple formulas. My issue is finding something that can assign a dynamic array. Tried a Vlookup but since some Amp settings may apply to multiple breakers, I will not receive an exact match. For example, the settings 300A is in F11, G7 and H4.

Tried an Index Match Match. In this case, I can find the associated column / array but then I struggle with the row.



Find Setting.xlsx