INDEX MATCH from table with multiple MATCH criteria

Hi Guys,

Back again. Today I'm struggling a bit with data I'm trying to extract from a table with multiple MATCH criteria. File attached.

I need to determine what diameter pipe is needed based on the following factors.

The total LU (cell J24) must be less or equal than the max load (row 11)
The pipe length (H24) must be less or equal than the max length in row 14 (or ignored if no value in 14 is shown)
The highest single LU (F24) must not be less or equal than the highest load (row 12)
The diameter (M24) is then matched to the Diameter in (row 13)

Hope you can help and appreciate the input in advance

You need to provide the expected result, with reasons!

I am not sure that one exists in your sample! Which makes it difficult to solve

how do you match the diameter in row 13, there is not an exact match

Hi Davsth,

Thanks for the reply.

Sorry, my bad, the values in the example should have been an expected result but I now see it is not.

In the example the total LU (J24) is 47, so immediately the expected Diameter is 28mm.
The length is 13 but 28mm has no Max length so it can be ignored.
The Highest LU (F24) is 13, but 28mm also has no highest value LU so can be ignored.

I have attached a better example and will add a description now.

The Total LU is 6 (J24)
The Length is 7 (H24)
The Highest LU is 6 (F24)

Total LU of 6 would indicate that 15mm is sufficient
Length of 7 is equal or lower than the max length of 7 for 15mm
Highest LU of 6 is higher than Highest value allowed of 4 however, so the pipe size would have to be 22mm in this case.

I've made a few more changes to make things easer to understand, apologises. This is straight out of a British Standard so clear as mud as always

Thanks for the input

Your logic and explanations still do not reconcile or at least are unclear

Total LU of 6 would indicate that 15mm is sufficient why I can see that 6 corresponds to a diameter of 15 in column I, however columns D to I all satisify this conditions, do you mean closest to < or equal
Length of 7 is equal or lower than the max length of 7 for 15mm true but also for e and also f if it is just less than
Highest LU of 6 is higher than Highest value allowed of 4 however, so the pipe size would have to be 22mm in this case. well yes 22

what does < or ignored actually mean?

A guess is entered as an array with shift control enter

=MIN(MIN(IF((H24<=D14:O14)*(F24<=D12:O12)*(J24<=O11:O11)>0,D13:O13,99999)),MIN(IF((D14:O14="")*(F24<=D12:O12)*(J24<=O11:O11)>0,D13:O13,99999)))

or in case it cann't be solved

=IF(MIN(MIN(IF((H24<=D14:O14)*(F24<=D12:O12)*(J24<=O11:O11)>0,D13:O13,9999)),MIN(IF((D14:O14="")*(F24<=D12:O12)*(J24<=O11:O11)>0,D13:O13,99999)))=9999,"No solution",MIN(MIN(IF((H24<=D14:O14)*(F24<=D12:O12)*(J24<=O11:O11)>0,D13:O13,9999)),MIN(IF((D14:O14="")*(F24<=D12:O12)*(J24<=O11:O11)>0,D13:O13,99999))))

9999 is my unsolvable answer, so i recode to no solution