Index and match with several criteria!!Help

[l3lackdrake]

Hi

Im not very well use in the use of index and match but for the function i need to do this is the only possible way of doing it with out going into visual basic.

I currently have a table of different configuration, (Draft table) table been sorted by V column largest first


draft table.PNG


Currently im using severely fields that will be used to select the correct line (these criteria values change depending on other calculations)


critera.PNG

Currently I have had to use two extra fields to help me (current trip and Rated frame size)

current trip.PNG


frame size.PNG

Currently the frame size is looking up the trip current column as that's the one been sorted.

I would like to be able to get rid of these extra fields and just go off the original values (running current and rated amps) and also know how to add additional criteria like Voltage and kA

Here is my current Formula

=INDEX('Brackers&Fuses'!L3:L99,MATCH(1,(E44='Brackers&Fuses'!U3:U99)*(E42='Brackers&Fuses'!V3:V99)*(E40='Brackers&Fuses'!S3:S99),0))

This is currently give me a value of 82. As you can see i want to know add the voltage any device that has a higher number and also the kA rating any device that is higher

If the current values are use this should give me an answer of 91 (column L)

For the life of me i can seem to this to work and im running out of ideas. Please help

Also the table i would like to be able to add data to it would having to do any sorting, if its possible.

Regards

Daniel