Can the index match formula look for multiple criteria and then return the same criteria as the value?

jacquesB · Jan 15, 2016

Hi Guys

I am trying to write an index match formula that can return several values and look for several critieria. I believe that the best approach would be an array formula, but cannot get it fully right, so I thought that I would turn to this forum.

I want the formula in column M to return all the values from column J that are also present in column D.

Below in the example, I have typed in manually how it should preferably return the value.

Thank you!

Book1

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

2

3

4

Missing info

ID Responsible

Peter

Dan

5

Type A

Type B

Type C

Peter

Dan

Type A

Type B

Type C

Type A

Type B

Type C

6

1

12019

1

11168

1

10996

4489

11168

12019

7

2

12021

2

11185

2

12021

8

625

12023

625

11201

9

632

12123

632

11593

10

670

12124

670

12421

11

671

12125

671

10995

12

672

12135

11076

672

10996

13

1170

12136

11083

1170

10997

14

1189

12182

11087

1189

10999

15

1214

12224

11091

1214

11005

16

1249

12249

11094

1249

11058

17

1250

12839

11119

4489

11593

18

2849

13045

11121

6676

12421

19

2911

13062

11123

6679

12463

20

3238

11666

11006

6955

12659

21

3884

11041

10954

7214

12775

22

4489

11667

11024

10954

11185

23

6676

11790

4489

10996

12136

24

6679

11894

6676

10997

12182

25

6955

11932

6679

10999

6676

26

7214

11949

6955

11006

11168

27

11168

11979

7214

11024

11201

28

11185

12014

11168

11060

12021

29

11201

11060

11185

11121

6955

30

11593

12015

11201

11123

7214

31

12421

11562

11593

11562

12123

32

12463

11616

12421

11616

12124

33

12659

11623

10995

11623

12125

34

12775

11624

10996

11624

12135

35

12796

10996

10997

12014

12019

36

12799

10997

10999

12015

12023

37

12815

10999

11005

12016

6679

38

14019

12016

11058

14019

12016

39

40

Ark1

Weazel · Jan 15, 2016

maybe something like...

M6=IFERROR(INDEX($J$6:$J$38,SMALL(IF(ISNUMBER(MATCH($J$6:$J$38,$D$6:$D$38,0)),ROW($J$6:$J$38)-ROW($J$6)+1),ROWS($M$6:M6))),"") control shift enter

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A

[/TD]
[TD]

B

[/TD]
[TD]

C

[/TD]
[TD]

D

[/TD]
[TD]

E

[/TD]
[TD]

F

[/TD]
[TD]

G

[/TD]
[TD]

H

[/TD]
[TD]

I

[/TD]
[TD]

J

[/TD]
[TD]

K

[/TD]
[TD]

L

[/TD]
[TD]

M

[/TD]
[TD]

N

[/TD]
[TD]

O

[/TD]
[TD]

P

[/TD]
[TD]

Q

[/TD]
[TD]

R

[/TD]
[TD]

S

[/TD]
[TD]

T

[/TD]
[/TR]
[TR]
[TD]

4

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Missing info

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

ID Responsible

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dan[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Type A[/TD]
[TD]Type B[/TD]
[TD]Type C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Peter[/TD]
[TD]Dan[/TD]
[TD][/TD]
[TD]Type A[/TD]
[TD]Type B[/TD]
[TD]Type C[/TD]
[TD][/TD]
[TD][/TD]
[TD]Type A[/TD]
[TD]Type B[/TD]
[TD]Type C[/TD]
[/TR]
[TR]
[TD]

6

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1

[/TD]
[TD]

12019

[/TD]
[TD]

12019

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1

[/TD]
[TD]

11168

[/TD]
[TD][/TD]
[TD]

1

[/TD]
[TD]

10996

[/TD]
[TD]

4489

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

11168

[/TD]
[TD]

12019

[/TD]
[TD]

12019

[/TD]
[/TR]
[TR]
[TD]

7

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

2

[/TD]
[TD]

12021

[/TD]
[TD]

12021

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

2

[/TD]
[TD]

11185

[/TD]
[TD][/TD]
[TD]

2

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12021

[/TD]
[TD]

12021

[/TD]
[/TR]
[TR]
[TD]

8

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

625

[/TD]
[TD]

12023

[/TD]
[TD]

12023

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

625

[/TD]
[TD]

11201

[/TD]
[TD][/TD]
[TD]

625

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

632

[/TD]
[TD]

12123

[/TD]
[TD]

12123

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

632

[/TD]
[TD]

11593

[/TD]
[TD][/TD]
[TD]

632

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

670

[/TD]
[TD]

12124

[/TD]
[TD]

12124

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

670

[/TD]
[TD]

12421

[/TD]
[TD][/TD]
[TD]

670

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

671

[/TD]
[TD]

12125

[/TD]
[TD]

12125

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

671

[/TD]
[TD]

10995

[/TD]
[TD][/TD]
[TD]

671

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

672

[/TD]
[TD]

12135

[/TD]
[TD]

11076

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

672

[/TD]
[TD]

10996

[/TD]
[TD][/TD]
[TD]

672

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1170

[/TD]
[TD]

12136

[/TD]
[TD]

11083

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1170

[/TD]
[TD]

10997

[/TD]
[TD][/TD]
[TD]

1170

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1189

[/TD]
[TD]

12182

[/TD]
[TD]

11087

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1189

[/TD]
[TD]

10999

[/TD]
[TD][/TD]
[TD]

1189

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1214

[/TD]
[TD]

12224

[/TD]
[TD]

11091

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1214

[/TD]
[TD]

11005

[/TD]
[TD][/TD]
[TD]

1214

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1249

[/TD]
[TD]

12249

[/TD]
[TD]

11094

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1249

[/TD]
[TD]

11058

[/TD]
[TD][/TD]
[TD]

1249

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1250

[/TD]
[TD]

12839

[/TD]
[TD]

11119

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

4489

[/TD]
[TD]

11593

[/TD]
[TD][/TD]
[TD]

4489

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

2849

[/TD]
[TD]

13045

[/TD]
[TD]

11121

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

6676

[/TD]
[TD]

12421

[/TD]
[TD][/TD]
[TD]

6676

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

2911

[/TD]
[TD]

13062

[/TD]
[TD]

11123

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

6679

[/TD]
[TD]

12463

[/TD]
[TD][/TD]
[TD]

6679

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

3238

[/TD]
[TD]

11666

[/TD]
[TD]

11006

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

6955

[/TD]
[TD]

12659

[/TD]
[TD][/TD]
[TD]

6955

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

3884

[/TD]
[TD]

11041

[/TD]
[TD]

10954

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

7214

[/TD]
[TD]

12775

[/TD]
[TD][/TD]
[TD]

7214

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

4489

[/TD]
[TD]

11667

[/TD]
[TD]

11024

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

10954

[/TD]
[TD]

11185

[/TD]
[TD][/TD]
[TD]

14019

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

6676

[/TD]
[TD]

11790

[/TD]
[TD]

4489

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

10996

[/TD]
[TD]

12136

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

6679

[/TD]
[TD]

11894

[/TD]
[TD]

6676

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

10997

[/TD]
[TD]

12182

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

25

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

6955

[/TD]
[TD]

11932

[/TD]
[TD]

6679

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

10999

[/TD]
[TD]

6676

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

26

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

7214

[/TD]
[TD]

11949

[/TD]
[TD]

6955

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

11006

[/TD]
[TD]

11168

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

27

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

11168

[/TD]
[TD]

11979

[/TD]
[TD]

7214

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

11024

[/TD]
[TD]

11201

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

28

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

11185

[/TD]
[TD]

12014

[/TD]
[TD]

11168

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

11060

[/TD]
[TD]

12021

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

29

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

11201

[/TD]
[TD]

11060

[/TD]
[TD]

11185

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

11121

[/TD]
[TD]

6955

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

30

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

11593

[/TD]
[TD]

12015

[/TD]
[TD]

11201

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

11123

[/TD]
[TD]

7214

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

31

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12421

[/TD]
[TD]

11562

[/TD]
[TD]

11593

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

11562

[/TD]
[TD]

12123

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

32

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12463

[/TD]
[TD]

11616

[/TD]
[TD]

12421

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

11616

[/TD]
[TD]

12124

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

33

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12659

[/TD]
[TD]

11623

[/TD]
[TD]

10995

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

11623

[/TD]
[TD]

12125

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

34

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12775

[/TD]
[TD]

11624

[/TD]
[TD]

10996

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

11624

[/TD]
[TD]

12135

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

35

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12796

[/TD]
[TD]

10996

[/TD]
[TD]

10997

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12014

[/TD]
[TD]

12019

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

36

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12799

[/TD]
[TD]

10997

[/TD]
[TD]

10999

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12015

[/TD]
[TD]

12023

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

37

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12815

[/TD]
[TD]

10999

[/TD]
[TD]

11005

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12016

[/TD]
[TD]

6679

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

38

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

14019

[/TD]
[TD]

12016

[/TD]
[TD]

11058

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

14019

[/TD]
[TD]

12016

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

jacquesB · Jan 17, 2016

Hey Weazel.

Thanks for the answer! It only returns 671 from D11 and it does it all the way when I drag it down the column.

jacquesB · Jan 17, 2016

Wait! I managed to fix it. Last row formula should be rows rather than row.

Thank you so much. You have saved me for hours of work!

Can the index match formula look for multiple criteria and then return the same criteria as the value?

jacquesB

New Member

Weazel

Well-known Member

jacquesB

New Member

jacquesB

New Member

Similar threads

Share this page

Can the index match formula look for multiple criteria and then return the same criteria as the value?

jacquesB

New Member

Weazel

Well-known Member

jacquesB

New Member

jacquesB

New Member

Similar threads

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