Return first value (duplicates) based on multiple criterias

[krdka]

Hi alle, hope someone can help me 

I’m having trouble returning the first value, amongst duplicates based on multiple criterias

I have 3 sheets, where 2 of the sheets are raw data and the third is one where I’ve combined the necessary data I need.

S3 looks like the following:
Name---------- Month---------- Year---------- Salary---------- Hours---------- Account
Anne---------- 09--------------------2013---------- 5500---------- 145-------------------- ??
Thomas----------09--------------------2013---------- 3500---------- 134-------------------- ??
Anne---------- 10--------------------2013---------- 5400---------- 135-------------------- ??
Beth---------- 10-------------------- 2013---------- 2000---------- 100-------------------- ??

Salary is from S1, and Hours and Account are from S2.
I’ve had to sum the hours, so that it would only occur in one row. S2 looks like this.
Name---------- Month---------- Year-------------------- Hours---------- Account
Anne---------- 09--------------2013-------------------- 45-------------------- 500
Anne---------- 09--------- -----2013-------------------- 50-------------------- 500
Anne---------- 09-------- -------2013------------------ 50-------------------- 500
Thomas----------09---- ------------2013------------------ 69-------------------- 500
Thomas----------09----------------2013-------------------- 30-------------------- 500
Thomas----------09----------------2013-------------------- 5-------------------- 500
Anne---------- 10---------------2013-------------------- 120-------------------- 510
Anne---------- 10---------------2013-------------------- 15-------------------- 510
Beth---------- 10--------------- 2013-------------------- 60-------------------- 500
Beth---------- 10--------------- 2013-------------------- 40-------------------- 500

My task now is to return the accounnumber from S2 into S3. Because they are duplicates, I can’t seem to figure it out.
I’ve tried with an if & and function, like:
If(and(s2year=s3year,s2month=s3month,s2name=s3name,s1account=500),500,510)
But it only returns 510 all the way down. I’ve tried other ways and googling it, but I can’t seem to figure it out. I would rather not remove the duplicates, because as I said it’s the raw data, and I don’t want to touch that. Hope someone can help me 

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[sktneer]

Try this go get the account number.......

Please Login or Register  to view this content.

This is an array formula so it needs to be confirmed with Ctrl+Shift+Enter and then drag down.

Please find the attached sheet to see if this is what you want.

[krdka]

Hi
Thanks for the quick response.

I have attached a workbook. krdka_excelforum.xlsx

Hope this is enough,

Sincerely, Kristine

[Pepe Le Mokko]

Does the attached help ?

[krdka]

Hmm no, it doesn't. The value should be 500 or 510, depending on what it is on S2. It looks good until H16, but then the values are much higher than 500.

[Pepe Le Mokko]

Is your calculation set to Automatic?

[krdka]

I'm sorry, what does that mean?

[krdka]

Screen Shot 2013-12-15 at 21.01.37.png

Don't know if it looks the same on my end, as in yours? But the values should match the values in red.

[Pepe Le Mokko]

I thought I had misunderstood your question, now I'm sure of it

[krdka]

Thanks for your help

I've figured it out myself, in the meantime. A bit "clumsy" way, without any fancy formulas. But basically I sum up all the numbers with same criteria - count how many of these occurences are there - and divide them by each other. So it gives me an average, which turns out to be either 500 or 510. As I said - very simple, but it solves the problem :D