AnotherManipulator
New Member
- Joined
- Nov 20, 2017
- Messages
- 5
Hey, I've inherited a formula I would like a little more understanding on. Hopefully you can help me translate it!
To give you some background on the data, I have two sheets.
IW47 - This contains a list of jobs arranged by the person who completed them.
Column A is where the Job number is located.
Column E is where the Name of the person is located.
The same Job number can be attributed to more than one person.
IW38 - This gives more detail about the jobs themselves so all I am interested in is matching the people to the job.
Column A again contains the Job Number.
The Formula
I am essentially working from IW38 sheet and attempting to pull information from IW47 sheet. As more than one person can be on a job, I have copied the above formula into the adjacent columns and changed the below, as highlighted in bold:
And then repeated this all the way to J, giving me a possible 10 people it can check for.
This however seems to be a little hit and miss with the results, I would guess that 70% of the time the formulas report correctly, other times it's wildly wrong and I cannot fathom why. I'm sure it's to do with the above bolded part but now I need a little help in understanding what this formula is actually doing, 'under the hood' to remedy it.
Should I bin this off and work on an Index/Match formula instead or is there something glaringly obvious I'm missing.
Many thanks in advance!
To give you some background on the data, I have two sheets.
IW47 - This contains a list of jobs arranged by the person who completed them.
Column A is where the Job number is located.
Column E is where the Name of the person is located.
The same Job number can be attributed to more than one person.
IW38 - This gives more detail about the jobs themselves so all I am interested in is matching the people to the job.
Column A again contains the Job Number.
The Formula
Code:
{=IFERROR(INDEX('IW47'!$E$2:$E$9145,SMALL(IF('IW38'!A2='IW47'!$A$2:$A$9145,ROW('IW47'!$A$2:$A$9145)-ROW(A2)+1),COLUMN(A1))),"")}
I am essentially working from IW38 sheet and attempting to pull information from IW47 sheet. As more than one person can be on a job, I have copied the above formula into the adjacent columns and changed the below, as highlighted in bold:
Code:
{=IFERROR(INDEX('IW47'!$E$2:$E$9145,SMALL(IF('IW38'!A2='IW47'!$A$2:$A$9145,ROW('IW47'!$A$2:$A$9145)-ROW[B](B2)[/B]+1),COLUMN[B](B1)[/B])),"")}
And then repeated this all the way to J, giving me a possible 10 people it can check for.
This however seems to be a little hit and miss with the results, I would guess that 70% of the time the formulas report correctly, other times it's wildly wrong and I cannot fathom why. I'm sure it's to do with the above bolded part but now I need a little help in understanding what this formula is actually doing, 'under the hood' to remedy it.
Should I bin this off and work on an Index/Match formula instead or is there something glaringly obvious I'm missing.
Many thanks in advance!