Hi There
I have a formula that is doing exactly what I want when on a set table on the same sheet where I can manually define the arrays... see below
Sample Original Data: Table3
<tbody>
</tbody>
I am using this formula {=IFERROR((INDEX(B$47:B$57,SMALL(IF($B$40=$H$47:$K$57,ROW($H$47:$H$57)-ROW($B$47)+1),ROW(1:1)))),"BLANK")}
which when copied across a group of cells gives me the following sample result: where $b$40= "Peter"
Sample Result
<tbody>
</tbody>
Now for my question: I would like to use the same formula, but use structured references... Ultimately I need the logic as follows: If $b$40 is found in Table3[SPLIT 1 NAME] or Table3[SPLIT 2 NAME] then show selected column entries
hope this makes sense
Thanks
Peter
I have a formula that is doing exactly what I want when on a set table on the same sheet where I can manually define the arrays... see below
Sample Original Data: Table3
| A43 | B | C | D | E | F | G | H | I | J | K | L | M |
| 44 | ||||||||||||
| 45 | Month | Type | Candidate | Client | TOTAL REVENUE | SPLIT 1 NAME | SPLIT 1 % DEAL | SPLIT 1 REVENUE | SPLIT 2 NAME | SPLIT 2 % DEAL | SPLIT 2 REVENUE | |
| 46 | ||||||||||||
| 47 | September | Temp | Paul | sam co 1 | $ 2,251.20 | Peter | 100% | $ 2,251.20 | - | 0% | $ - | |
| 48 | September | Temp | Brenda | Sam co 1 | $ 217.20 | Peter | 100% | $ 217.20 | - | 0% | $ - | |
| 49 | September | Temp | jose | test comp | $ 861.72 | Peter | 100% | $ 861.72 | - | 0% | $ - | |
| 50 | September | Temp | Shelley | test comp | $ 1,078.85 | Peter | 100% | $ 1,078.85 | - | 0% | $ - | |
| 51 | September | Temp | ruby | great company | $ 1,111.00 | Peter | 100% | $ 1,111.00 | - | 0% | $ - | |
| 52 | September | Temp | 0 | 0 | $ 500.00 | Peter | 100% | $ 500.00 | - | 0% | $ - | |
| 53 | September | Temp | 0 | 0 | $ - | steve | 100% | $ 5,000.00 | Peter | 0% | $ 1,000.00 | |
| 54 | September | Temp | 0 | 0 | $ - | Steve | 100% | $ - | - | 0% | $ - | |
| 55 | September | Temp | 0 | 0 | $ - | Peter | 100% | $ - | - | 0% | $ - | |
| 56 | September | Temp | Kopi | waster land | $ 252.50 | Pam | 100% | $ 252.50 | - | 0% | $ - | |
| 57 | September | Temp | Mark | mcdonalds | $ 312.71 | Pam | 100% | $ 312.71 | - | 0% | $ - |
<tbody>
</tbody>
I am using this formula {=IFERROR((INDEX(B$47:B$57,SMALL(IF($B$40=$H$47:$K$57,ROW($H$47:$H$57)-ROW($B$47)+1),ROW(1:1)))),"BLANK")}
which when copied across a group of cells gives me the following sample result: where $b$40= "Peter"
Sample Result
| September | Temp | 0 | Paul | sam co 1 | 2251.2 | Peter | 1 | 2251.2 | - | 0 | 0 | 0 | 0 | 0 | 0 |
| September | Temp | 0 | Brenda | Sam co 1 | 217.2 | Peter | 1 | 217.2 | - | 0 | 0 | 0 | 0 | 0 | 0 |
| September | Temp | 0 | jose | test comp | 861.72 | Peter | 1 | 861.72 | - | 0 | 0 | 0 | 0 | 0 | 0 |
| September | Temp | 0 | Shelley | test comp | 1078.85 | Peter | 1 | 1078.85 | - | 0 | 0 | 0 | 0 | 0 | 0 |
| September | Temp | 0 | ruby | great company | 1111 | Peter | 1 | 1111 | - | 0 | 0 | 0 | 0 | 0 | 0 |
| September | Temp | 0 | 0 | 0 | 500 | Peter | 1 | 500 | - | 0 | 0 | 0 | 0 | 0 | 0 |
| September | Temp | 0 | 0 | 0 | 0 | steve | 1 | 5000 | Peter | 0 | 1000 | 0 | 0 | 0 | 0 |
| September | Temp | 0 | 0 | 0 | 0 | Peter | 1 | 0 | - | 0 | 0 | 0 | 0 | 0 | 0 |
<tbody>
</tbody>
Now for my question: I would like to use the same formula, but use structured references... Ultimately I need the logic as follows: If $b$40 is found in Table3[SPLIT 1 NAME] or Table3[SPLIT 2 NAME] then show selected column entries
hope this makes sense
Thanks
Peter
