Formula to return list of matches

[Phil_SA]

Hi - I've got a list of countries that correspond to certain numbers, where some numbers correspond to more than one country. I need to create a lookup on a separate tab that will list all the names that correspond to a certain number.

Example where Denmark is A1 and "3" is in B1

[TABLE="width: 289"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Denmark[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Norway[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Sweden[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Netherlands[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]STATE OF QATAR[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Hong Kong[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]NEW ZEALAND[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Belgium[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Finland[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Kuwait[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]EMIRATE OF ABU DHABI[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]South Korea[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]UNITED KINGDOM[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

On the next tab, I want to be able to look up all countries that are a specific number linked to the previous tab. I've tried to use some form of Index Match but haven't gotten any success. Any help would be great! Thanks.

 

[Oeldere]

with a pivot table.

Book1
	G	H	I	J
1	country	number		ROW
2	Denmark	3		3
3	Norway	3		Denmark
4	Sweden	3		Netherlands
5	Netherlands	3		Norway
6	United States	4		Sweden
7	STATE OF QATAR	6		4
8	Hong Kong	7		United States
9	NEW ZEALAND	7		6
10	Belgium	8		STATE OF QATAR
11	Austria	8		7
12	Finland	8		Hong Kong
13	Kuwait	9		NEW ZEALAND
14	EMIRATE OF ABU DHABI	9		8
15	France	9		Austria
16	South Korea	10		Belgium
17	UNITED KINGDOM	10		Finland
18				9
19				EMIRATE OF ABU DHABI
20				France
21				Kuwait
22				10
23				South Korea
24				UNITED KINGDOM
25				Eindtotaal
26
sheet2

 

[Oeldere]

alternative with index / match

Book1
	G	H	I	J	K
1	country	number	help		3
2	Denmark	3	31		Denmark
3	Norway	3	32		Norway
4	Sweden	3	33		Sweden
5	Netherlands	3	34		Netherlands
6	United States	4	41
7	STATE OF QATAR	6	61
8	Hong Kong	7	71
9	NEW ZEALAND	7	72
10	Belgium	8	81
11	Austria	8	82
12	Finland	8	83
13	Kuwait	9	91
14	EMIRATE OF ABU DHABI	9	92
15	France	9	93
16	South Korea	10	101
17	UNITED KINGDOM	10	102
sheet2

Cell Formulas
Range	Formula
K2	=IFERROR(INDEX($G$1:$I$17,MATCH(($K$1&ROW()-1)*1,$I$1:$I$17,0),1),"")
I2	=(H2&COUNTIF($H$2:$H2,H2))*1

 

[Aladin Akyurek]

Let A1:B16 house the source data.

Let A1 of Sheet2 house 3.

In A3 of Sheet2 just enter:

=COUNTIFS(Sheet1!$B$1:$B$16,$A1)

In A4 of Sheet2 control+shift+enter, not just enter, and copy down:

=IF(ROWS(A$4:A4)<=A$3,INDEX(Sheet1!$A$1:$A$16,SMALL(IF($B$1:$B$16=A$1,
    ROW(Sheet1!$A$1:$A$16)-ROW(Sheet1!$A$1)+1),ROWS(A$4:A4))),"")