# Count and Lookup?

#### Darts101

##### New Member
Name Draw
RICK 1
DAN 4
TONY 5
BRANDON 3
MIKE 2
CARL 6

I have this list of names (Column A) with a random number (Column B) assigned to each person. The quantity of names could be different and the row the Draw numbers are located could be different. I need to count how many names are present and then bring back two names with the 1 & 2 combined, 3 & 4 combined and so forth.

Rick & Mike
Brandon & Dan
Tony & Carl

Any help will be greatly appreciated.

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
This isn't the most elegant solution, but if you can put in 2 helper columns you could do something like this:
Excel Workbook
ABCDEF
1NameDrawHelper ColumnsJoined Names
2RICK112RICK & MIKE
3DAN434BRANDON & DAN
4TONY556TONY & CARL
5BRANDON3
6MIKE2
7CARL6
Sheet1
Excel 2010
Cell Formulas
RangeFormula
F2=INDEX(\$A\$2:\$B\$7,MATCH(\$D2,\$B\$2:\$B\$7,0),1)&" & "&INDEX(\$A\$2:\$B\$7,MATCH(\$E2,\$B\$2:\$B\$7,0),1)
F3=INDEX(\$A\$2:\$B\$7,MATCH(\$D3,\$B\$2:\$B\$7,0),1)&" & "&INDEX(\$A\$2:\$B\$7,MATCH(\$E3,\$B\$2:\$B\$7,0),1)
F4=INDEX(\$A\$2:\$B\$7,MATCH(\$D4,\$B\$2:\$B\$7,0),1)&" & "&INDEX(\$A\$2:\$B\$7,MATCH(\$E4,\$B\$2:\$B\$7,0),1)
D3=E2+1
D4=E3+1
E3=D3+1
E4=D4+1

Hope that helps.

Excel Workbook
ABCD
1NameDrawCombo
2RICK1RICK & MIKE
3DAN4BRANDON & DAN
4TONY5TONY & CARL
5BRANDON3
6MIKE2
7CARL6
...
Cell Formulas
RangeFormula
D2=IF((COUNTA(A:A)-1)/2A1), "", INDEX(A:A,MATCH(ROW(A1)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A1)*2,B:B,0)))
D3=IF((COUNTA(A:A)-1)/2A2), "", INDEX(A:A,MATCH(ROW(A2)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A2)*2,B:B,0)))
D4=IF((COUNTA(A:A)-1)/2A3), "", INDEX(A:A,MATCH(ROW(A3)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A3)*2,B:B,0)))
D5=IF((COUNTA(A:A)-1)/2A4), "", INDEX(A:A,MATCH(ROW(A4)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A4)*2,B:B,0)))
D6=IF((COUNTA(A:A)-1)/2A5), "", INDEX(A:A,MATCH(ROW(A5)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A5)*2,B:B,0)))
D7=IF((COUNTA(A:A)-1)/2A6), "", INDEX(A:A,MATCH(ROW(A6)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A6)*2,B:B,0)))

Claymationator
Looks like it would work but would rather not use helpers unless absolutely needed. Thanks for your reply.

AlphaFrog
Your solution works well. I need to add another factor into it though. In the event that there are more than 6 names in column A it would leave it blank. I am basically entering this to a different cell if there are not 6. Would COUNTIF work instead of the COUNTA.

Just copy the formula down as many cells as you need. If can handle more than six names.

It brings back the values no matter how many names are entered. I need it to bring the names back only if there are 6, if there are more or less than 6 names then I need them to be blank.

To clarify a little of what I am trying to do. The names will be on a different sheet than where entered and the number of entries decides which sheet they will be entered on.

Last edited:
Cell D2
=IF(OR(COUNTA(A:A)-1<>6, ROW(A1)*2>6), "", INDEX(A:A,MATCH(ROW(A1)*2-1,B:B,0))&" & "&INDEX(A:A,MATCH(ROW(A1)*2,B:B,0)))

Change the six to the number of entries you want to test for.

Perfect, thanks for the help with this. I was trying to use a countif statement and could not get it right. I can never get the index/match to work right either unless I have a starting point.

Thanks again for the help.

Replies
14
Views
2K
Replies
7
Views
303
Replies
19
Views
648
Replies
1
Views
99
Replies
0
Views
266

1,211,697
Messages
6,103,360
Members
447,861
Latest member
LllopezXC

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