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?
 
barry houdiniConnect With a Mentor Commented:
Try this formula in C2 copied down
=IF(B2="KRF",VLOOKUP(A2,areahead,2,FALSE),VLOOKUP($B2,headcount,2,FALSE))
regards, barry
0
 
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
 
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
All Courses

From novice to tech pro — start learning today.