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

mister_m · Sep 16, 2023

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 · Sep 16, 2023

W

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

Glenn Kennedy said:
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.

I tried it and it worked!!
You made my day! Thank you very much.
Would you explaining how this formula works? Especially when he aggregate, and the frequency function?

mister_m · Sep 16, 2023

Peter_SSs said:
What if the gear ratio is 30? Both 28 and 32 are exactly the same distance away. Which should be used and why?

In such cases, we prefer the one with lower gear ratio.
The reason is that lower gear ratio would mean we get higher output speed after reduction. This can be brought down to desired value using other simple methods like a VFD.
If we had chosen the one with higher gear ratio, the output rotation speed would itself be low. And this would have to be increased somehow to reach the desired value. (The required ratios are calculated based on maximum speed of the motor.)

Peter_SSs · Sep 17, 2023

So, wouldn't this considerably shorter one do the same job?
=INDEX(C2:H2,MATCH(TRUE,INDEX(C3:H8,MATCH(B14,B3:B8),0)>=B15,0))

mister_m · Sep 18, 2023

Peter_SSs said:
So, wouldn't this considerably shorter one do the same job?
=INDEX(C2:H2,MATCH(TRUE,INDEX(C3:H8,MATCH(B14,B3:B8),0)>=B15,0))

I had tried to do something similar but had struggle entering the 'array' parameter for the row since it is variable.

As to the one you posted, it is still finding the next lowest even if the required value is extremely close to the next highest.
Closest match is the first priority.

Peter_SSs · Sep 18, 2023

mister_m said:
Closest match is the first priority.

Ah, yes I forgot about that, sorry. Try this.

Excel Formula:

=INDEX(C2:H2,MATCH(TRUE,INDEX(C3:H8,MATCH(MIN(ABS(B14-B3:B8)),ABS(B14-B3:B8),0),0)>=B15,0))

mister_m · Sep 18, 2023

Peter_SSs said:
Ah, yes I forgot about that, sorry. Try this.

Excel Formula:

=INDEX(C2:H2,MATCH(TRUE,INDEX(C3:H8,MATCH(MIN(ABS(B14-B3:B8)),ABS(B14-B3:B8),0),0)>=B15,0))

Will give it a try and let you know.

lagardem · Sep 19, 2023

Hi there !
Thanks for your various answers !
I am having the same type of issue, but with multiple criteria
Objective: retrieve the name of the state (row D16:AC16), that has the maximum/highest value (found in C20:AC120) from a selected criteria (A6 to A10 per ex), selected within column A (A19:A124).
The search thus goes horizontally/vertically/left/right
Any idea of how it could be solved ?
Thanks a lot in advance !
Cheers

NB: I tried these, but I failed somehow :P :
=XLOOKUP(($A$20:$A$124=A6)*(MAX(IF($A$20:$A$124=A6;$C$20:$AC$124));$C$16:$AC$16))
=INDEX($C$16:$AC$16;MATCH(MAXIFS($C$19:$AC$124;$A$19:$A$124;A6);$C$19:$AC$124;0))
=INDEX($C$16:$AC$16;MATCH(MAX(IF($A$19:$A$124=A6;$C$19:$AC$124));$C$19:$AC$124;0))
=INDEX($C$16:$AC$16;MATCH(MAX(IF($A$19:$A$124=A7;$C$19:$AC$124));$C$19:$AC$124;0))
=INDEX($16:$16;AGGREGATE(15;6;COLUMN($C$16:$AC$16)/(INDEX($C$20:$AC$124;MATCH(LOOKUP(0;0/FREQUENCY(0;ABS($A$20:$A$124-A6));$A$20:$A$124);$A$20:$A$124;0)=INDEX($AD$20:$AD$124;MATCH(A6;$A$20:$A$124;0));1))))
=INDEX($14:$14;AGGREGATE(15;6;COLUMN($C$14:$AC$14)/(INDEX($C$20:$AC$124;MATCH(LOOKUP(0;0/FREQUENCY(0;ABS($A$20:$A$124-A6));$A$20:$A$124);$A$20:$A$124;0)=E6;1))))

INDEX MATCH MAXIFS_Multiple criteria.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

2

How could I retrieve the state with the highest values of the below criteria (D6 to D10), all at once, without adding columns AD and following ?

3

4

5

Highest value INDEX MATCH

State INDEX MATCH

All at once formula ? :)

6

Surfaces agricoles en %

56

FR

7

Surfaces boisées en %

51

TI

8

par km²

5 300

BS

9

selon le Code pénal (CP)

109

BS

10

selon la Loi sur les stupéfiants et les produits psychotropes (LStup)

18

BS

11

12

13

14

RAW DATA

ADDED COLUMNS

15

16

Choix d'indicateurs en comparaison régionale, 2021

Année

Suisse

ZH

BE

LU

UR

SZ

OW

NW

GL

ZG

FR

SO

BS

BL

SH

AR

AI

SG

GR

AG

TG

TI

VD

VS

NE

GE

JU

MAX

Related state

MIN

Related state

17

18

19

Population

20

Habitants en milliers

2019

8 606.0

1 539.3

1 039.5

413.1

36.7

160.5

37.9

43.1

40.6

127.6

321.8

275.2

195.8

289.5

82.3

55.4

16.1

510.7

199.0

685.8

279.5

351.5

805.1

345.5

176.5

504.1

73.6

21

Variation en %

2010-2019

9.4

12.1

6.1

9.4

3.6

9.4

6.6

5.0

5.1

12.9

15.5

7.8

5.9

5.5

7.8

4.6

2.8

6.6

3.3

12.2

12.5

5.3

12.9

10.5

2.6

10.1

5.1

22

par km²

2019

215.2

926.8

178.0

289.1

34.7

188.5

78.9

178.5

59.6

616.2

202.0

348.2

5 300.2

559.2

276.2

228.3

93.5

261.8

28.0

491.6

323.9

128.2

285.4

66.3

246.3

2 050.8

87.8

23

Répartition par âge en %

2019

24

0–19

20.0

19.8

19.0

20.3

20.0

19.3

20.0

18.1

19.2

20.3

22.2

19.0

17.3

19.2

18.7

20.2

21.1

20.5

17.8

20.2

20.3

18.0

21.9

19.6

21.0

20.8

25

20–64

61.4

63.2

59.9

61.9

59.2

62.6

60.7

61.0

60.5

62.4

61.8

61.1

62.9

58.7

59.9

59.3

61.0

60.5

61.8

59.1

61.5

60.5

59.8

62.5

57.9

26

65 ou plus

18.7

17.0

21.1

17.8

20.8

18.2

19.3

20.9

20.4

17.3

16.0

19.9

19.8

22.2

21.4

19.9

19.6

18.5

21.7

17.9

23.0

16.6

19.9

19.3

16.4

21.3

27

Population urbaine en % 1)

2019

84.8

99.3

74.7

63.9

88.6

82.1

27.6

50.5

76.7

100.0

74.8

85.9

100.0

97.6

89.7

76.6

0.0

82.4

44.9

85.0

67.0

92.8

89.6

78.9

87.8

100.0

53.7

28

Etrangers en %

2019

25.3

27.1

16.6

18.8

12.6

21.9

14.7

14.8

24.2

28.3

22.8

22.9

36.6

23.1

26.1

16.5

11.3

24.4

18.8

25.3

25.2

27.6

33.0

22.6

25.3

40.0

14.7

29

Mouvement de la population (en ‰)

2019

30

Taux brut de nuptialité

4.5

5.2

4.4

4.8

4.6

5.0

4.3

4.9

4.2

4.6

4.9

4.4

4.9

5.0

4.9

5.0

4.3

4.7

4.9

3.4

3.9

4.2

3.6

4.3

3.7

31

Taux brut de divortialité

2.0

1.9

1.6

1.3

1.9

1.4

2.1

1.4

1.9

2.0

1.8

2.0

1.8

2.0

1.0

1.9

1.7

2.0

1.9

2.1

2.2

2.3

32

Taux brut de natalité

10.0

10.8

9.6

10.6

9.4

9.6

9.2

8.9

10.2

10.4

10.9

9.5

10.5

8.8

10.1

10.8

10.3

8.2

10.3

10.4

7.1

10.7

9.1

9.5

10.7

9.6

33

Taux brut de mortalité

7.9

7.4

9.3

7.5

8.4

6.9

7.4

7.8

10.3

6.2

6.6

8.4

10.6

8.6

9.8

8.7

8.3

7.9

8.8

7.3

7.5

9.2

6.8

8.4

9.0

6.7

9.1

34

Ménages privés en milliers

2019

3 811

692

475

179

15

69

16

19

18

55

134

123

97

129

38

24

7

223

92

297

122

164

354

153

82

203

32

35

Taille moyenne des ménages en personnes

2019

2.2

2.1

2.3

2.2

2.3

2.4

2.2

1.9

2.2

2.1

2.3

2.4

2.3

2.1

2.3

2.1

2.2

2.1

2.4

2.2

36

37

Langue principale en % 2) 3)

2019

38

Allemand

62.7

80.7

83.7

88.6

93.4

88.3

92.0

90.6

86.5

80.3

26.1

87.1

76.8

86.2

86.8

90.8

94.9

87.7

75.2

86.4

89.1

10.2

5.5

24.6

4.7

4.0

7.4

39

Français

22.8

3.3

11.0

1.6

X

1.9

(1.5)

(1.6)

(1.2)

3.4

69.0

2.9

5.0

3.2

(1.8)

(1.1)

X

1.3

2.0

1.3

4.7

82.6

67.8

87.7

79.6

89.8

40

Italien

8.3

5.6

3.1

3.3

(1.4)

3.5

(1.2)

(3.2)

5.5

4.3

2.4

4.8

6.1

5.5

3.9

(2.3)

(1.5)

3.5

13.2

5.1

3.7

88.6

5.0

4.6

5.7

6.4

2.5

41

Romanche

0.5

0.3

(0.1)

(0.2)

X

(0.3)

(0.6)

X

(0.6)

(0.2)

(0.1)

(0.2)

(0.1)

X

(0.4)

14.7

(0.1)

(0.0)

X

(0.1)

X

42

Anglais

6.5

9.5

4.4

4.6

(3.3)

6.3

(3.5)

5.4

(4.0)

12.1

3.9

4.3

12.1

6.7

5.2

4.6

(3.3)

4.1

3.4

5.5

3.9

8.9

3.8

4.6

12.2

(2.3)

Data OFS

Cell Formulas
Range	Formula
B6:B10	B6	=INDEX($AD$20:$AD$124,MATCH(A6,$A$20:$A$124,0))
C6:C10	C6	=INDEX($AE$20:$AE$124,MATCH(A6,$A$20:$A$124,0))
AD20	AD20	=MAX(D20:AC20)
AE20:AE42	AE20	=IFERROR(INDEX($D$16:$AC$16,MATCH(AD20,D20:AC20,0)),"N/A, nfwd")
AF20:AF42	AF20	=MIN(C20:AC20)
AG20:AG42	AG20	=IFERROR(INDEX($D$16:$AC$16,MATCH(AF20,D20:AC20,0)),"N/A, nfwd")
AD21:AD42	AD21	=MAX(C21:AC21)

Fluff · Sep 19, 2023

Please start a thread of your own for this question & stop posting it to multiple old threads.

Also I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

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

mister_m

New Member

Attachments

mister_m

New Member

Excel Facts

mister_m

New Member

Peter_SSs

MrExcel MVP, Moderator

mister_m

New Member

Peter_SSs

MrExcel MVP, Moderator

mister_m

New Member

lagardem

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

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

New Member

Attachments

New Member

Excel Facts

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock