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

[jacquesB]

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	12019				1	11168		1	10996	4489			11168	12019	12019
7			2	12021	12021				2	11185		2						12021	12021
8			625	12023	12023				625	11201
9			632	12123	12123				632	11593
10			670	12124	12124				670	12421
11			671	12125	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]

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

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

T​

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​

12019​

1​

11168​

1​

10996​

4489​

11168​

12019​

12019​

7​

2​

12021​

12021​

2​

11185​

2​

12021​

12021​

8​

625​

12023​

12023​

625​

11201​

625​

9​

632​

12123​

12123​

632​

11593​

632​

10​

670​

12124​

12124​

670​

12421​

670​

11​

671​

12125​

12125​

671​

10995​

671​

12​

672​

12135​

11076​

672​

10996​

672​

13​

1170​

12136​

11083​

1170​

10997​

1170​

14​

1189​

12182​

11087​

1189​

10999​

1189​

15​

1214​

12224​

11091​

1214​

11005​

1214​

16​

1249​

12249​

11094​

1249​

11058​

1249​

17​

1250​

12839​

11119​

4489​

11593​

4489​

18​

2849​

13045​

11121​

6676​

12421​

6676​

19​

2911​

13062​

11123​

6679​

12463​

6679​

20​

3238​

11666​

11006​

6955​

12659​

6955​

21​

3884​

11041​

10954​

7214​

12775​

7214​

22​

4489​

11667​

11024​

10954​

11185​

14019​

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​

<tbody>
</tbody>

 

[jacquesB]

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]

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!