Index Match

[hajiali]

1	MIKE	HAT	JIM	BAT	JAMES	BAT	BOB	HAT
2	BOB	BAT	JAMES	HAT	BILL	HAT
3	JIM	HAT
4	MIKE	BAT	JAMES	HAT	BOB	BAT
5	BILL	HAT	MIKE	HAT

<tbody>
</tbody>

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.

2
MIKE
JIM
JAMES	HAT
BILL	HAT
BOB	BAT
PHILIP

<tbody>
</tbody>

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

4
MIKE	BAT
JIM
JAMES	HAT
BILL
BOB	HAT
PHILIP

<tbody>
</tbody>

 

[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)),"")