#### floridagunner

##### Board Regular
Hello I need some help adjusting a formula.

I have a worksheet named (FA_Split_Master_July1007). Which has this information:

(code)
A B C
12345 TOM
12345 KYLE
12345 DAN
12345 ALEX
67899 TAYLOR
67899 KURT
88425 MIKE
(/code)

In another sheet I have this Information:

(code)
A P
12345
12345
12345
12345
12345
12345
12345
12345
67899
67899
67899
67899
(/code)

I applying this formula to bring in the corresponding names:

(code)=INDEX(FA_Split_Master_July1007!\$A\$1:\$C\$57670,SMALL(IF(FA_Split_Master_July1007!\$A\$1:\$C\$57670=\$A2,ROW(FA_Split_Master_July1007!\$A\$1:\$C\$5767)),COUNTIF(\$A\$2:A2,A2)),3)(/code)

However this is the result that I get back:
(code)
A P
12345 TOM
12345 KYLE
12345 DAN
12345 ALEX
12345 #NUM!
12345 #NUM!
12345 #NUM!
12345 #NUM!
67899 TAYLOR
67899 KURT
67899 #NUM!
67899 #NUM!
(/code)

What I need the formula to do is this:

(code)
A P
12345 TOM
12345 KYLE
12345 DAN
12345 ALEX
12345 TOM
12345 KYLE
12345 DAN
12345 ALEX
67899 TAYLOR
67899 KURT
67899 TAYLOR
67899 KURT
(/code)

Does anybody know what I could change in my formula to make it work?

Thanks

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### barry houdini

##### MrExcel MVP
Perhaps try

=INDEX(FA_Split_Master_July1007!\$A\$1:\$C\$57670,SMALL(IF(FA_Split_Master_July1007!\$A\$1:\$C\$57670=\$A2,ROW(FA_Split_Master_July1007!\$A\$1:\$C\$5767)),MOD(COUNTIF(\$A\$2:A2,A2)-1,COUNTIF(FA_Split_Master_July1007!\$A\$1:\$A\$57670,A2))+1),3)

#### floridagunner

##### Board Regular
Hello Barry thanks for the formula but I cant seem to work out the concept behind your adjustment.

Its not working in my spreadsheet but I am thinking maybe it only needs a couple of Adjustments.

Did you try it? Did it work on your sheet?

#### floridagunner

##### Board Regular
hold on there Barry I think I was wrong. I think it may actually be working after all.

Let me check more closely and I will confirm with you.

THANKS

#### floridagunner

##### Board Regular

This is pretty strange but, whats happening is that when I get to row 757 the formula does not work anymore.

Even though it bought back correctly all the previous 756 names.

Thanks

#### barry houdini

##### MrExcel MVP
I can't think of anything obvious that would cause that. What's in A757 and what result do you get with the formula on that row and subsequent rows?

Is the formula exactly the same as the one I posted?

#### floridagunner

##### Board Regular
Ok I found the mistake.

One of the range references was \$A\$1:\$C\$5767.

It should have been \$A\$1:\$C\$57670

THANKS AGAIN BARRY. I WAS STRUGGLING WITH THIS FORMULA FOR THE LAST 7 HOURS OR SO.

THANKS

#### floridagunner

##### Board Regular
OH AND BY THE WAY YES IT HAS BOUGHT BACK ALL THE NAMES NOW SO ITS WORKING PERFECTLY.

THANKS AGAIN

Replies
3
Views
127
Replies
3
Views
109
Replies
4
Views
494
Replies
3
Views
122
Replies
12
Views
608

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,160
Messages
5,768,530
Members
425,480
Latest member
br400821

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