Excel 2007 : Collecting Information

[SoulRebel]

Dear All,

I need a formula that can collect the necessary information as shown in the attachment.

Thank you for your cooperation in advance !

example.xlsx

[Fotis1991]

Hi and welcome to the forum

For login: =INDEX($E$3:$E$6;MATCH(B18;$B$3:$B$6;0))

For logout: =INDEX($G$3:$G$6;MATCH(B18;$B$3:$B$6;1000))

Change the semi-colon, to gomma if you have to do it.

Hope to helps you.

[SoulRebel]

Dear Fotis,

Thank you for the quick response. The formula that you have provided is working, but it is my mistake that I did not explain the whole situation.

First - there are several spreadsheets. Raw data is one and the result should be on another.

Second - names are randomly multiplied(It can appear only once, but it can appear 2,3,5,etc times).

I am attaching new Example file with the new configuration.

Thank you for the efforts !

example.xlsx

[Fotis1991]

Not sure,if i follow you...

Please, take a look to the example.

Is it ok?

[SoulRebel]

Dear Fotis,

Thank you very much for the formula. It is working almost perfect The only thing that needs to be fixed is the following:

If we search for person who is not in the raw data, there is no problem for the log in table(because it has 0 at the end for exact mach)

=IFERROR(INDEX(LogInOut!$D$1:$D$150,MATCH(B4,LogInOut!$A$1:$A$150,0)),"")

But for the log out table if the person is missing it just assigns to him value from present one.

0169.xls

Please advice. Thank you !

[Fotis1991]

Hi again.

For login you can put this.

=IF(COUNTIF(Sheet2!$A$3:$A$19;C13)=0;"";INDEX(Sheet2!$D$3:$D$19;MATCH(Sheet1!C13;Sheet2!$A$3:$A$19;0)))

For Logout.

=IF(COUNTIF(Sheet2!$A$3:$A$19;C13)=0;"";INDEX(Sheet2!$F$3:$F$19;MATCH(Sheet1!C13;Sheet2!$A$3:$A$19;100000)))

Is it OK, now?

[SoulRebel]

Dear Fotis,

Thank you !

It is working perfectly now.

[Fotis1991]

You are welcome!