Lookup to match orginisation chart against employee listing

[hughzy]

Hi There

I want to be able to perform a lookup function to search against an orginsation chart based on an employee's unique number. In another sheet i want to compare an organised list that has salary information and employee numbers and pull the FTE value from the org chart.

Because the org charts arent in a uniform i am having issues using a normal index match function. Any suggestions how to create a formula.

The below example has the employee number in the 1st column and the result i wish to retrieve being the 1 value in the 4th column. The orginisation chart has lots of people with the same format however spread across varying location in the sheet. I believe i am having issues as my current formula =INDEX(Editorial!$B$7:$BT$59,MATCH('TEC Feb'!A2,Editorial!$B$7:$BT$59,)) dosent take into account the column match and i am not sure how to have two match functions with the same index. I am also unsure if having my array the same as the lookup field is incorrect. Apologies if my understanding or language used is incorrect.

26689 1.0
Editor (ABC)
Amie Hickland

14464 1.0
Editor (ATN)
Rob McKay

Regards
Andrew

[kvsrinivasamurthy]

Post a sample file showing manually what are the expected results.

[hughzy]

I have attached a condensed file showing what i am trying to achieve. The attached is a smaller version of a large file which isnt practical to completely reformat.

[humdingaling]

using CSE formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Please Login or Register  to view this content.

ive broken it down in two columns in the example file to see how this formula is constructed

essentially
the helper column finds the column in which the staff member number is
then using offset mixed in with match/index formula does the rest

[Teethless mama]

None volatile solution:

=INDEX('ORG Chart'!B$1:Q$33,MAX(IF('ORG Chart'!B$1:Q$33=A2,ROW('ORG Chart'!B$1:Q$33))),MAX(IF('ORG Chart'!B$1:Q$33=A2,COLUMN('ORG Chart'!B$1:Q$33)))+1)


Array Formula: Press Ctrl+Shift+Enter, not just Enter

[humdingaling]

uses mama's solution its much easier to use and understand

[kvsrinivasamurthy]

ARRAY formula in 2nd row then drag down.

Please Login or Register  to view this content.

This ARRAY formula also works

Please Login or Register  to view this content.