Index Match with conditions

[vblackman]

Hello to All...

I have 2 spreadsheets on the firts one I have this:

Names Monday Tuesday Wed

Group (1)
Carla P LOA
George P LOA P
John P LOA P

Group (2)
Carla P
Robert P LOA P
Mike P P

But then, I am using in second separate sheet with a list of all my employee organized aphabetic so that I make sure I am not missing an employee on my report by groups.
the problem is that because this employee switch from one job to another I have duplicates names on the same sheet ( example Carla)..
so when I use INDEX MATCH on my second sheet.. I does not come out the "P" of present or LOA for Carla for example,
It comes out "0" (cero) because it brings the info from the empty cell ( the job where the employee did not work on that day) ..
there is any kind of condition I can use to have this worked out or it is just impossible?
Thanks to all for any kind of info.. this will really help me lots at work.
Giannina

[MarvinP]

Hi vblackman,

Excel has an answer for you. It is called Pivot Tables from Multiple Ranges..

See http://www.contextures.com/xlPivot08.html

I've added the icon for this function on my Quick Access Toolbar (QAT). See if this doesn't do what you need.

The test of a good product is that it has stuff you need but have never even heard of before. Excel is cool!

[vblackman]

Thanks Marvin... but It did not work for me.. : (

[Cutter]

If its just a small number of groups then maybe something like:

=IFERROR(VLOOKUP("Carla",Sheet1!A2:D4,4,0),"")&IFERROR(VLOOKUP("Carla",Sheet1!A8:D10,4,0),"")

[vblackman]

Thanks... I think i did not explain my self properly.. I am attaching my spreadsheet hoping that maybe you can have a look at it and se if maybe I am asking for too much to excel. There are 3 tabs, 1st Tab Base Plant Week 20, Second Tab Serp Project -Week 20, and 3rd Tab is for my balancing sheet, like I said before I have around 51 employees switching between jobs, what I would like is to find out how to Balance my force report every day on my 3rd Tab called Balance sheet,

It is hard to go checking one by one every day to make sure that for all my employee I have a "P" of Present, "ABS" of absent, "LOA" of leave of absence, "QUIT", etc.

When I use the Term "ONLOAN" on the tab SERP Project is so that the client knows he wil not pay for that employee on that that particular day because we loan the employee to a different area, but if this is the case the employee ON LOAN has to show on on the Tab Called Base Plant Week 20, I have missed employee assistance many times because It hard to know if I am missing an employee when I have duplicates in the tab Base Plant ( duplicates are marked in red)

I know is confusing but maybe you can help me out!!!
Thanks in advance for your help and any recomendation you could have for me!
:D
now I am not sure if the attachment in on here .. but if not I will try again!!

[Cutter]

Unless all those employees have given consent to their names being shown, you'd better remove that file and post one with fictitious names.

[vblackman]

:s I dont know how ro remove it!!!
:s

[Cutter]

I see you figured it out.
OK, a formula approach could be like this:

In E3 (and dragged over and down) this:

Please Login or Register  to view this content.

This will return, if a match is found, a value from Base Plant and a / and a value from SERP Project. So a value left of the / means it was found on Base Plant and a value right of the / means it was found on SERP Project.

[vblackman]

Ok I got it now .. thanks for your advice! Its my first time doing this stuff! I have attached the new one.

[Cutter]

The formula I gave in Post #8 refers to your actual file (which I will now delete) and should have the whole column references that I used changed to specific ranges.

[vblackman]

Thanks Cutter.. I tried but some how is only working in certain cells, not for all of them.. I am not sure what it could be wrong ..I underatand your logic.. I will keep checking it.

[vblackman]

In fact the matching values are only coming up for the employees that are in both sheets(SERP & Base Plant), but the employee that are only in either of them ( they dont switch) are not coming up.. I guess is too complicate it for excel?

[Cutter]

I got your file out of my Recycle Bin and had another look at it. It's not working because I missed the fact that the same employee can show up on the same worksheet. That being the case the formula is only returning the first occurrence of the employee on each sheet.
So, this will require assistance above my "expertise". Maybe MarvinP will provide further assistance.