# 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.

#### Attachments

• 10.4 KB Views: 11

#### AliGW

##### Active Member
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).

#### bosco_yip

##### Excel Ninja
Or...……..

H3, copied down :

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

Regards
Bosco

#### ExcelSur

##### Member
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.

#### ExcelSur

##### Member
Or...……..

H3, copied down :

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

Regards
Bosco

Hi Bosco
This also worked. As always thanks for your help.

#### Peter Bartholomew

##### Well-Known Member
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.

#### ExcelSur

##### Member
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.
Hello Peter.
This also worked. Thanks for the solution.

#### bosco_yip

##### Excel Ninja
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

#### Peter Bartholomew

##### Well-Known Member
@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: