Index formula, maybe?

[crave4excel]

Hi
I'm trying to create a formula that auto "pull in" names that are assigned to a specific group number. For example, i have a table below to start off -

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]type[/TD]
[TD]group[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]orange A[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]orange b[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]orange c[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]orange d[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I then want to create a formula that automatically pulls in the type name (2nd column) based on the group number I entered in the 1st column.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]group [/TD]
[TD]type[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]orange a[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]orange c[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]orange d[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]orange b[/TD]
[/TR]
</tbody>[/TABLE]

I'm not sure if this can be achieved at all, hoping the Excel gurus out there can lend me a hand! Appreciated any help in advance.

Regards,

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B
1	type	group
2	orange A	2
3	orange b	4
4	orange c	2
5	orange d	2
6
7
8	group	type
9	2	orange A
10	2	orange c
11	2	orange d
12	4	orange b
Sheet