• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF Function

ExcelSur

Member
Hello Experts
I have a spreadsheet with over 5000 rows with employee data, but I dont have a department and manager data in the spreadsheet for each employee. I have over 60 employees with over 15 managers. I want to create a column with Department and Manager. I have attached a sample file with desired out highlighted.

Thanks for your help
 

Attachments

  • chandoo employee data.xlsx
    10.4 KB · Views: 11
This will do it:

=INDEX($M$4:$M$8,MATCH(C3,$K$4:$K$8,0))&" - "&INDEX($L$4:$L$8,MATCH(C3,$K$4:$K$8,0))

Your thread title is exceptionally poor: it should tell us what you are trying to do, NOT how you think it should be done (which, in this case, is by no means the optimum).
 
This will do it:

=INDEX($M$4:$M$8,MATCH(C3,$K$4:$K$8,0))&" - "&INDEX($L$4:$L$8,MATCH(C3,$K$4:$K$8,0))

Your thread title is exceptionally poor: it should tell us what you are trying to do, NOT how you think it should be done (which, in this case, is by no means the optimum).

Thanks Ali, that worked. Will write a better title going forward.
 
Typed in cell G3 and allowed to spill down
= XLOOKUP( Employee, Hierachy, CONCATENATE(Department, " - ", Manager ) )
At last, a context in which CONCATENATE is useful!
This is for Office 365 only.
 
Another option to use VLOOKUP function

In G3, array formula copied down :

=VLOOKUP(C3,IF({1,0},$K$4:$K$8,$M$4:$M$8&" - "&$L$4:$L$8),2,0)

This is an array formula, needs to confirm by pressing Ctrl + Shift + Enter 3 keystrokes together

Regards
Bosco
 
@ExcelSur
Congratulation on even finding out that it worked; my Excel programming style is far from mainstream and I didn't even define the Names when I posted. Mind you, it should be easier than the formula I have just tested:
Code:
= LET(
\0, "This formula accumulates CAPEX and Depreciation separately, then combines them",
     CumulativeExpenditure, IFERROR(
          ACCUMULATE( 0, 0, CAPEX ),
          SUMIFS( CAPEX, period#, "<="&period# ) ),
     CumulativeDepreciation, IFERROR(
          ACCUMULATE( 0, 0, Depreciation# ),
          SUMIFS( Depreciation#, period#, "<="&period# ) ),
     Assets, CumulativeExpenditure - CumulativeDepreciation,
Assets )

It would be a miracle if it were even recognised as an Excel formula.
Note: The LET function is currently on beta release within the O365 insider channel.
 
Last edited:
Back
Top