Index Match

[hajiali]

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]MIKE[/TD]
[TD]HAT[/TD]
[TD]JIM[/TD]
[TD]BAT[/TD]
[TD]JAMES[/TD]
[TD]BAT[/TD]
[TD]BOB[/TD]
[TD]HAT[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BOB[/TD]
[TD]BAT[/TD]
[TD]JAMES[/TD]
[TD]HAT[/TD]
[TD]BILL[/TD]
[TD]HAT[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]JIM[/TD]
[TD]HAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]MIKE[/TD]
[TD]BAT[/TD]
[TD]JAMES[/TD]
[TD]HAT[/TD]
[TD]BOB[/TD]
[TD]BAT[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BILL[/TD]
[TD]HAT[/TD]
[TD]MIKE[/TD]
[TD]HAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

above table Column A is day of month column followed by names than code to the right for each person. on another sheet I have the list of all the names in cell (A2) down. In cell (A1) I input Day of Month. based on the (A1), in column B i want to it pull the code next to each name that appears for that day of month. Ex. If I put 2 in cell (A1) results should be below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIKE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JIM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JAMES[/TD]
[TD]HAT[/TD]
[/TR]
[TR]
[TD]BILL[/TD]
[TD]HAT[/TD]
[/TR]
[TR]
[TD]BOB[/TD]
[TD]BAT[/TD]
[/TR]
[TR]
[TD]PHILIP[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

IF I change cell (A1) to 4 the results would read:

[TABLE="width: 500"]
<tbody>[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIKE[/TD]
[TD]BAT[/TD]
[/TR]
[TR]
[TD]JIM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JAMES[/TD]
[TD]HAT[/TD]
[/TR]
[TR]
[TD]BILL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOB[/TD]
[TD]HAT[/TD]
[/TR]
[TR]
[TD]PHILIP[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Re: Index Match Help

With data like

Book1
	A	B	C	D	E	F	G	H	I
1
2	1	MIKE	HAT	JIM	BAT	JAMES	BAT	BOB	HAT
3	2	BOB	BAT	JAMES	HAT	BILL	HAT
4	3	JIM	HAT
5	4	MIKE	BAT	JAMES	HAT	BOB	BAT
6	5	BILL	HAT	MIKE	HAT
Sheet1

try

Book1
	A	B
1	4
2	MIKE	BAT
3	JIM
4	JAMES	HAT
5	BILL
6	BOB	BAT
7	PHILIP
Sheet2

Cell Formulas
Range	Formula
B2	=IFERROR(INDEX(Sheet1!$C$2:$I$6,$A$1,MATCH(A2,INDEX(Sheet1!$B$2:$I$6,$A$1,0),0)),"")