# Index/Match Help

#### AnotherManipulator

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

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

#### AnotherManipulator

##### New Member
Perfect thanks again. You may now throw your beard over your shoulder and consider this solved...

Replies
2
Views
182
Replies
10
Views
398
Replies
2
Views
437
Replies
5
Views
143
Replies
2
Views
209

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.

### Which adblocker are you using?

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

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