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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,498
Office Version
  1. 365
Platform
  1. Windows
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))),"")}
 

AnotherManipulator

New Member
Joined
Nov 20, 2017
Messages
5
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
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,498
Office Version
  1. 365
Platform
  1. Windows
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?

Yes, exactly that :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,735
Messages
5,638,064
Members
417,001
Latest member
MSteel

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
Top