Index Match :(

[Kamran01]

Dear Experts,

Please have a look on below chart, this is showing date wise in and out.

S.No.

Employee

Name

Father/Name

Designation

01

01

02

02

03

03

04

04

05

05

In

Out

In

Out

In

Out

In

Out

In

Out

1

187430

Andress Mashi

Iqbal Mashi

Sweeper

8:05

20:15

Sunday

8:18

19:01

8:05

19:08

8:11

19:19

2

170602

Muhammad Ismail

Sawan

Sweeper

8:07

20:23

Sunday

8:07

20:23

8:07

20:23

8:07

20:23

3

189363

Abdul Khaliq

Laal Buksh

Sweeper

8:05

20:15

Sunday

8:05

20:15

8:05

20:15

8:05

20:15

4

154396

Muhammad Zahid

Abdul Hameed

Sweeper

8:05

20:15

Sunday

8:18

19:01

8:05

19:08

8:11

19:19

<colgroup><col><col><col><col><col><col span="10"></colgroup><tbody>
</tbody>

I want result accordingly, how i can use index match function easily.

EBS #	EMP. NAME	FATHER NAME	DESIGNATION	DATE	IN	OUT
187430	Andress Mashi	Iqbal Mashi	Sweeper	01-Sep-18	8:05 AM	8:15 PM
187430	Andress Mashi	Iqbal Mashi	Sweeper	02-Sep-18	Holiday	Holiday
187430	Andress Mashi	Iqbal Mashi	Sweeper	03-Sep-18	8:18 AM	7:01 PM
187430	Andress Mashi	Iqbal Mashi	Sweeper	04-Sep-18	8:05 AM	7:08 PM
187430	Andress Mashi	Iqbal Mashi	Sweeper	05-Sep-18	8:11 AM	7:19 PM

<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

Thanks
Best Regard,
Kamran noor

 

[steve the fish]

Heres a few to try.

In A11:

=INT((ROW(A1)-1)/5)+1

In B11:

=INDEX(B$3:B$6,$A11)

copy across to E11.

In F11:

=INDEX($F$3:$O$6,$A11,MOD(ROWS($A$1:A1)*2-1,10))

In G11:

=INDEX($F$3:$O$6,$A11,1+MOD(ROWS($A$1:A1)*2-1,10))

Drag them all down until you get errors.