If formula?

[Mitch123]

I have two parts to my problem.
I'm trying to return the answer shown in Column 4 (C) below depending on the criteria from Column 2 and Column 3. I also want the answers to be in sequential order depending on the result from the previous.
As you can see in Column 4, if the criteria is (A, John) then the answer = A1. If its (A, bob) then it = A2, likewise (A,harry) = A3. You will also see that row 7 = B1 because that was the answer in row 5.

Does anyone know the formula?

<table style="width: 197px; height: 180px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 47pt;" width="63"> <col style="width: 12pt;" width="16"> <col style="width: 29pt;" width="39"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 47pt;" height="20" width="63"> </td> <td style="vertical-align: top;">
</td><td class="xl65" style="width: 12pt;" width="16">A</td> <td style="vertical-align: top;">
</td><td class="xl65" style="width: 29pt;" width="39">B </td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl65" style="width: 48pt;" width="64">C</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" align="right" height="20">1</td> <td style="vertical-align: top;">
</td><td>A</td> <td style="vertical-align: top;">
</td><td>john</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>A1</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" align="right" height="20">2</td> <td style="vertical-align: top;">
</td><td>A</td> <td style="vertical-align: top;">
</td><td>john</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>A1</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" align="right" height="20">3</td> <td style="vertical-align: top;">
</td><td>A</td> <td style="vertical-align: top;">
</td><td>bob</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>A2</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" align="right" height="20">4</td> <td style="vertical-align: top;">
</td><td>A</td> <td style="vertical-align: top;">
</td><td>harry</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>A3</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" align="right" height="20">5</td> <td style="vertical-align: top;">
</td><td>B</td> <td style="vertical-align: top;">
</td><td>jill</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>B1</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" align="right" height="20">6</td> <td style="vertical-align: top;">
</td><td>B</td> <td style="vertical-align: top;">
</td><td>don</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>B2</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" align="right" height="20">7</td> <td style="vertical-align: top;">
</td><td>B</td> <td style="vertical-align: top;">
</td><td>jill</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>B1</td><td style="vertical-align: top;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" align="right" height="20">8</td> <td style="vertical-align: top;">
</td><td>B</td> <td style="vertical-align: top;">
</td><td>larry</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td>B3</td><td style="vertical-align: top;">
</td> </tr> </tbody></table>

 

[AlphaFrog]

There may be a better way to do this, but this is what i came up with...

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Group</td><td style="font-weight: bold;text-align: center;;">Name</td><td style="font-weight: bold;text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">A</td><td style="text-align: center;;">john</td><td style="text-align: center;;">A1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">A</td><td style="text-align: center;;">john</td><td style="text-align: center;;">A1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">A</td><td style="text-align: center;;">bob</td><td style="text-align: center;;">A2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">A</td><td style="text-align: center;;">harry</td><td style="text-align: center;;">A3</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">B</td><td style="text-align: center;;">jill</td><td style="text-align: center;;">B1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">B</td><td style="text-align: center;;">don</td><td style="text-align: center;;">B2</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">B</td><td style="text-align: center;;">jill</td><td style="text-align: center;;">B1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">B</td><td style="text-align: center;;">larry</td><td style="text-align: center;;">B3</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">{=IF(<font color="Blue">SUM(<font color="Red">(<font color="Green">$A$2:A2=A2</font>)*(<font color="Green">$B$2:B2=B2</font>)</font>)=1,
A2&SUM(<font color="Red">IF(<font color="Green">FREQUENCY(<font color="Purple">IF(<font color="Teal">$A$2:A2=A2,MATCH(<font color="#FF00FF">$B$2:B2,$B$2:B2,0</font>)</font>),ROW(<font color="Teal">$B$2:B2</font>)</font>),1</font>)</font>),
INDEX(<font color="Red">$C$1:C1,MATCH(<font color="Green">1,(<font color="Purple">$A$1:A1=A2</font>)*(<font color="Purple">$B$1:B1=B2</font>),0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table>
Copy the formula in C2 down column C.

 

[Mitch123]

Hi AlphaFrog,

thanks so much for your help. I'm new to this forum and appreciate all advice from advanced Excel users like yourself. I've tried you formula but it doesn't seem to work. Tried the Ctrl+Shift+Enter but it didn't add the {} to the formula. Tried it without the {} and this also didn't work. Any suggestions?

 

[AlphaFrog]

Hi AlphaFrog,

thanks so much for your help. I'm new to this forum and appreciate all advice from advanced Excel users like yourself. I've tried you formula but it doesn't seem to work. Tried the Ctrl+Shift+Enter but it didn't add the {} to the formula. Tried it without the {} and this also didn't work. Any suggestions?

• Select the cell with the formula (cell C2) .
• Click on the formula bar or press F2. This will put you in formula Edit mode.
• Press and hold the Ctrl key, then press and hold the Shift key, then press Enter.

When you press all three keys simultaneously, that's when you get the {}.