Do it with one formula
Enter in B6 and copy across and down
Formula:
=IFERROR(INDEX(Sega!$A:$D,SMALL(IF(ISNUMBER(SEARCH($C$3,Sega!$D$2:$D$419)),ROW(Sega!$D$2:$D$419)),ROWS(B$6:B6)),INDEX({4,3,1},COLUMNS($B:B))),"")
***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
v |
B |
C |
D |
3 |
Insert Game Here |
sonic |
|
4 |
|
|
|
5 |
Game |
System |
Region |
6 |
Sonic Adventure |
Dreamcast |
Europe |
7 |
Sonic Adventure 2 - Birthday Pack (Limited Edition) |
Dreamcast |
Japan |
8 |
Sonic The Hedgehog 2 |
Game Gear |
Europe |
9 |
Sonic The Hedgehog 2 |
Game Gear |
USA |
10 |
Sonic The Hedgehog Chaos |
Game Gear |
Europe |
11 |
Sonic Chaos |
Master System |
Europe |
12 |
Sonic the Hedgehog |
Master System |
Europe |
13 |
Sonic the Hedgehog 2 |
Master System |
Europe |
14 |
Sonic CD |
Mega CD |
Europe |
15 |
Sonic & Knuckles |
Mega Drive |
Europe |
16 |
Sonic 3D Flickie's Island |
Mega Drive |
Europe |
17 |
Sonic Spinball |
Mega Drive |
Europe |
18 |
Sonic The Hedgehog |
Mega Drive |
Europe |
19 |
Sonic The Hedgehog |
Mega Drive |
Japan |
20 |
Sonic The Hedgehog 2 |
Mega Drive |
Europe |
21 |
Sonic The Hedgehog 2 |
Mega Drive |
Japan |
22 |
Sonic The Hedgehog 3 |
Mega Drive |
Europe |
23 |
Sonic The Hedgehog 3 |
Mega Drive |
Japan |
24 |
Sonic Jam |
Sega Saturn |
Europe |
25 |
|
|
|
26 |
|
|
|
27 |
|
|
|
28 |
|
|
|
29 |
|
|
|
Bookmarks