Headcount Report

Hello,

I need some help.  I can create a formula, but I know that it's going to be a very nested IF/OR, Vlookup and I'm sure this can done easier with Index or Match, maybe.

I have a spreadsheet with a number of employee numbers.  I've used a vlookup formula to bring in the area (Column C on the headcount report) from the value on the Org Exec tab.  Here's the issue.  I have 9 Executives who are from our parent company (KRAFTS).  Their department number is KRF.  I have no way to give them an area, because they all have the same department number.  The executives are the heads of various areas, so I need to identify them in order to add them as a headcount.

The only distinct data point that I have are their employee numbers.  On the Org Exec tab, I've listed the areas, that they need to be associated with (Columns H,I).

I need a formula that says, if the employee number = "24433", then Corporate, else give me the area associated with the deparment number.  That's my thoughts, but I know that you experts, can probably come up with a 2 word formula that would solve this.
Headcount-Formula.xls
ablove3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello ablove,

I'm not sure if this is what you want: in C2 and copied down

=IFERROR(VLOOKUP($B2,headcount,2,FALSE),"")&IFERROR(" - "&VLOOKUP(A2,areahead,2,FALSE),"")

It will still list the Krafts as a company, but will then add a dash and the area if the employee number is found in the areahead table.

Is that something you can work with?

cheers, teylyn
0
barry houdiniCommented:
Try this formula in C2 copied down
=IF(B2="KRF",VLOOKUP(A2,areahead,2,FALSE),VLOOKUP($B2,headcount,2,FALSE))
regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Alternatively, this formula will just list the area

=IF(B2="KRF",IFERROR(VLOOKUP(A2,areahead,2,FALSE),""),IFERROR(VLOOKUP($B2,headcount,2,FALSE),""))

In C2 and copied down.

cheers, teylyn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.