Index Match for a 2D array, but with a twist.

[mister_m]

Hello. I'm new here and thankful for the quick registration.
I've come up with an interesting challenge while trying to solve this issue at work.

Attached image for reference.

I have two inputs (let's say in cells M4 and M5). M4: required ratio
M5: required torque.
I have a matrix C3 to H8. This matrix has torques in incremental order horizontally.
Row 2 contains model names of gearboxes. (C2 to H2)

I need to find the closest match for gear ratio in column B. Then, the next highest value for torque, corresponding to that ratio. Then, the model name for that torque from the row C2 to H2.

This seemed fairly simple using INDEX MATCH but got insanely complicated due to the required criteria.
Let me know if you need additional information.

 

[mister_m]

As an example, I input required ratio as 31 and torque as 21.
The result should be:
Selected ratio: 32 (because closest match)
Selected torque: 22 (next highest to required, in that row)
Gearbox model: MB4

 

[Glenn Kennedy]

Define what you mean by closest for the ratio. Closest above, closest below or closest, irrespective of side.

 

[mister_m]

Define what you mean by closest for the ratio. Closest above, closest below or closest, irrespective of side.

It could be closest above or below, side doesn't matter.

 

[Glenn Kennedy]

With ratio & torque in B14 & B15, respectively... try:

=INDEX(2:2,AGGREGATE(15,6,COLUMN(C2:H2)/(INDEX(C3:H8,MATCH(LOOKUP(0,0/FREQUENCY(0,ABS(B3:B8-B14)),B3:B8),B3:B8,0),)>=B15),1))

 

[mister_m]

With ratio & torque in B14 & B15, respectively... try:

=INDEX(2:2,AGGREGATE(15,6,COLUMN(C2:H2)/(INDEX(C3:H8,MATCH(LOOKUP(0,0/FREQUENCY(0,ABS(B3:B8-B14)),B3:B8),B3:B8,0),)>=B15),1))

Hmm.. I tried this one.
It results in error in value. Unsure of what's causing the issue.

 

[Glenn Kennedy]

Unfortunately, I can not get the mini-sheet uploader to work for me... so I cannot post a working file. Try setting it as an array formula (just in case!!) with CTRL-SHIFT-ENTER.

 

[mister_m]

Ah. Sure, will do.
But do you mean, it's working for you?

Unfortunately, I can not get the mini-sheet uploader to work for me... so I cannot post a working file. Try setting it as an array formula (just in case!!) with CTRL-SHIFT-ENTER.

 

[Glenn Kennedy]

Yes, it was fine (O365). I'm on the verge of abandoning Mr Excel, as I simply can't get the file uploader to work... no matter what I do.

 

[Peter_SSs]

Define what you mean by closest for the ratio.

It could be closest above or below, side doesn't matter.

So what if the gear ratio is 30? Both 28 and 32 are exactly the same distance away. Which should be used and why?