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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### 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
1
Views
63
Replies
3
Views
1K
Replies
4
Views
389
Replies
3
Views
134
Replies
4
Views
286

1,186,160
Messages
5,956,266
Members
438,244
Latest member
gaelen

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