Index/Match Help

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

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!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi, try this in B2 copied down and across - it should automatically adjust it's references accordingly as you do so.

{=IFERROR(INDEX('IW47'!$E$2:$E$9145,SMALL(IF($A2='IW47'!$A$2:$A$9145,ROW('IW47'!$A$2:$A$9145)-ROW('IW47'!$A$2)+1),COLUMNS($B2:B2))),"")}
 
Upvote 0
I've followed your instructions and early indications are that it's working so Thank you!

The formula actually starts in Column T and continues to AC so I'm guessing I need to amend the ($B2:B2) part to ($T2:T2) which will then look like ($T2:AC2) once copied across?

Thanks again
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top