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
[TABLE="width: 1004"]
<tbody>[TR]
[TD]A43
[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]44
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]45
[/TD]
[TD]Month[/TD]
[TD]Type[/TD]
[TD][/TD]
[TD]Candidate[/TD]
[TD]Client[/TD]
[TD]TOTAL REVENUE[/TD]
[TD]SPLIT 1 NAME[/TD]
[TD]SPLIT 1 % DEAL[/TD]
[TD]SPLIT 1 REVENUE[/TD]
[TD]SPLIT 2 NAME[/TD]
[TD]SPLIT 2 % DEAL[/TD]
[TD]SPLIT 2 REVENUE[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD]Paul
[/TD]
[TD]sam co 1[/TD]
[TD] $ 2,251.20[/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 2,251.20[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD]Brenda[/TD]
[TD]Sam co 1[/TD]
[TD] $ 217.20[/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 217.20[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]September[/TD]
[TD]Temp
[/TD]
[TD][/TD]
[TD]jose[/TD]
[TD]test comp[/TD]
[TD] $ 861.72[/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 861.72[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD]Shelley[/TD]
[TD]test comp[/TD]
[TD] $ 1,078.85[/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 1,078.85[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD]ruby[/TD]
[TD]great company[/TD]
[TD] $ 1,111.00[/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 1,111.00[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] $ 500.00[/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 500.00[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] $ - [/TD]
[TD]steve[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 5,000.00[/TD]
[TD]Peter[/TD]
[TD="align: right"]0%[/TD]
[TD] $ 1,000.00[/TD]
[/TR]
[TR]
[TD]54[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] $ - [/TD]
[TD]Steve[/TD]
[TD="align: right"]100%[/TD]
[TD] $ - [/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] $ - [/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ - [/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD]Kopi[/TD]
[TD]waster land[/TD]
[TD] $ 252.50[/TD]
[TD]Pam[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 252.50[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD]Mark[/TD]
[TD]mcdonalds[/TD]
[TD] $ 312.71[/TD]
[TD]Pam[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 312.71[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 1196"]
<tbody>[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD]Paul[/TD]
[TD]sam co 1[/TD]
[TD="align: right"]2251.2[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2251.2[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD]Brenda[/TD]
[TD]Sam co 1[/TD]
[TD="align: right"]217.2[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]217.2[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD]jose[/TD]
[TD]test comp[/TD]
[TD="align: right"]861.72[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]861.72[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD]Shelley[/TD]
[TD]test comp[/TD]
[TD="align: right"]1078.85[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1078.85[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD]ruby[/TD]
[TD]great company[/TD]
[TD="align: right"]1111[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1111[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]500[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]500[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]steve[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5000[/TD]
[TD]Peter[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 1004"]
<tbody>[TR]
[TD]A43
[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]44
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]45
[/TD]
[TD]Month[/TD]
[TD]Type[/TD]
[TD][/TD]
[TD]Candidate[/TD]
[TD]Client[/TD]
[TD]TOTAL REVENUE[/TD]
[TD]SPLIT 1 NAME[/TD]
[TD]SPLIT 1 % DEAL[/TD]
[TD]SPLIT 1 REVENUE[/TD]
[TD]SPLIT 2 NAME[/TD]
[TD]SPLIT 2 % DEAL[/TD]
[TD]SPLIT 2 REVENUE[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD]Paul
[/TD]
[TD]sam co 1[/TD]
[TD] $ 2,251.20[/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 2,251.20[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD]Brenda[/TD]
[TD]Sam co 1[/TD]
[TD] $ 217.20[/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 217.20[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]September[/TD]
[TD]Temp
[/TD]
[TD][/TD]
[TD]jose[/TD]
[TD]test comp[/TD]
[TD] $ 861.72[/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 861.72[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD]Shelley[/TD]
[TD]test comp[/TD]
[TD] $ 1,078.85[/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 1,078.85[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD]ruby[/TD]
[TD]great company[/TD]
[TD] $ 1,111.00[/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 1,111.00[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] $ 500.00[/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 500.00[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] $ - [/TD]
[TD]steve[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 5,000.00[/TD]
[TD]Peter[/TD]
[TD="align: right"]0%[/TD]
[TD] $ 1,000.00[/TD]
[/TR]
[TR]
[TD]54[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] $ - [/TD]
[TD]Steve[/TD]
[TD="align: right"]100%[/TD]
[TD] $ - [/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] $ - [/TD]
[TD]Peter[/TD]
[TD="align: right"]100%[/TD]
[TD] $ - [/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD]Kopi[/TD]
[TD]waster land[/TD]
[TD] $ 252.50[/TD]
[TD]Pam[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 252.50[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD]September[/TD]
[TD]Temp[/TD]
[TD][/TD]
[TD]Mark[/TD]
[TD]mcdonalds[/TD]
[TD] $ 312.71[/TD]
[TD]Pam[/TD]
[TD="align: right"]100%[/TD]
[TD] $ 312.71[/TD]
[TD]-[/TD]
[TD="align: right"]0%[/TD]
[TD] $ - [/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 1196"]
<tbody>[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD]Paul[/TD]
[TD]sam co 1[/TD]
[TD="align: right"]2251.2[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2251.2[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD]Brenda[/TD]
[TD]Sam co 1[/TD]
[TD="align: right"]217.2[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]217.2[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD]jose[/TD]
[TD]test comp[/TD]
[TD="align: right"]861.72[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]861.72[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD]Shelley[/TD]
[TD]test comp[/TD]
[TD="align: right"]1078.85[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1078.85[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD]ruby[/TD]
[TD]great company[/TD]
[TD="align: right"]1111[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1111[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]500[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]500[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]steve[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5000[/TD]
[TD]Peter[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]Temp[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Peter[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
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