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
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
Last edited by SoulRebel; 02-28-2012 at 10:25 AM.
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.
Last edited by Fotis1991; 02-28-2012 at 06:12 AM.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
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
Not sure,if i follow you...
Please, take a look to the example.
Is it ok?
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 !
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?
Dear Fotis,
Thank you !
It is working perfectly now.
You are welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks