Conditional Array formula

[serequiise]

Hi All.

I need an formula that will pull data from a sheet like this.

<colgroup><col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> <col style="mso-width-source:userset;mso-width-alt:2084; width:43pt" span="5" width="57"> <col style="mso-width-source:userset;mso-width-alt:3693;width:76pt" width="101"> <col style="mso-width-source:userset;mso-width-alt:2084; width:43pt" span="3" width="57"> </colgroup><tbody>
[TD="class: xl66, width: 57"]Metric 2
[/TD]
[TD="class: xl66, width: 57"]Metric 3[/TD]
[TD="class: xl66, width: 57"]Metric 4[/TD]
[TD="class: xl66, width: 57"]Metric 5[/TD]
[TD="class: xl66, width: 101"]Metric 6[/TD]
[TD="class: xl66, width: 57"]Metric 7[/TD]
[TD="class: xl66, width: 57"]Metric 8[/TD]
[TD="class: xl66, width: 57"]Metric 9[/TD]

[TD="class: xl66"]Metric A
[/TD]
[TD="class: xl66"]32
[/TD]
[TD="class: xl66"]44[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]40
[/TD]
[TD="class: xl66"]11[/TD]
[TD="class: xl66"]19[/TD]
[TD="class: xl66"]31[/TD]
[TD="class: xl66"]60[/TD]
[TD="class: xl66"]24[/TD]

[TD="class: xl66"]Matric B[/TD]
[TD="class: xl66"]26[/TD]
[TD="class: xl66"]47[/TD]
[TD="class: xl66"]42[/TD]
[TD="class: xl66"]46[/TD]
[TD="class: xl66"]50[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"]21[/TD]
[TD="class: xl66"]42[/TD]
[TD="class: xl66"]46
[/TD]

[TD="class: xl66"]Metric C[/TD]
[TD="class: xl66"]26[/TD]
[TD="class: xl66"]58[/TD]
[TD="class: xl66"]19[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]27[/TD]
[TD="class: xl66"]11[/TD]
[TD="class: xl66"]59[/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]45[/TD]

[TD="class: xl66"]Metric D[/TD]
[TD="class: xl66"]26[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]59[/TD]
[TD="class: xl66"]39[/TD]
[TD="class: xl66"]26[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]39[/TD]
[TD="class: xl66"]42[/TD]
[TD="class: xl66"]51[/TD]

[TD="class: xl66"]Metric E[/TD]
[TD="class: xl66"]30
[/TD]
[TD="class: xl66"]45[/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl66"]52[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]20[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]54[/TD]

[TD="class: xl65"]Total[/TD]
[TD="class: xl66"]43[/TD]
[TD="class: xl66"]50[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]40[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl65"]Return this cell[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl66"] [/TD]
[TD="class: xl66"]
[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl65"]Agent 2[/TD]
[TD="class: xl66"]Metric 1[/TD]
[TD="class: xl66"]Metric 2[/TD]
[TD="class: xl66"]Metric 3[/TD]
[TD="class: xl66"]Metric 4[/TD]
[TD="class: xl66"]Metric 5[/TD]
[TD="class: xl66"]Metric 6[/TD]
[TD="class: xl66"]Metric 7[/TD]
[TD="class: xl66"]Metric 8[/TD]
[TD="class: xl66"]Metric 9[/TD]

[TD="class: xl66"]Metric A[/TD]
[TD="class: xl66"]36[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]45[/TD]
[TD="class: xl66"]29[/TD]
[TD="class: xl66"]25[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]53[/TD]
[TD="class: xl66"]37[/TD]
[TD="class: xl66"]39[/TD]

[TD="class: xl66"]Matric B[/TD]
[TD="class: xl66"]41[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]38[/TD]
[TD="class: xl66"]32[/TD]
[TD="class: xl66"]54[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]26[/TD]

[TD="class: xl66"]Metric C[/TD]
[TD="class: xl66"]51[/TD]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]53[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]46[/TD]
[TD="class: xl66"]26[/TD]
[TD="class: xl66"]50[/TD]
[TD="class: xl66"]40[/TD]
[TD="class: xl66"]43[/TD]

[TD="class: xl65"]Total[/TD]
[TD="class: xl66"]42[/TD]
[TD="class: xl66"]39[/TD]
[TD="class: xl66"]19[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]29[/TD]
[TD="class: xl65"]Return this cell[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]
[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]
[/TD]
[TD="class: xl66"]
[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]

[TD="class: xl65"]Agent 3[/TD]
[TD="class: xl66"]Metric 1[/TD]
[TD="class: xl66"]Metric 2[/TD]
[TD="class: xl66"]Metric 3
[/TD]
[TD="class: xl66"]Metric 4[/TD]
[TD="class: xl66"]Metric 5
[/TD]
[TD="class: xl66"]Metric 6[/TD]
[TD="class: xl66"]Metric 7[/TD]
[TD="class: xl66"]Metric 8[/TD]
[TD="class: xl66"]Metric 9
[/TD]

[TD="class: xl66"]Matric B
[/TD]
[TD="class: xl66"]56[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]47[/TD]
[TD="class: xl66"]60[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]11[/TD]
[TD="class: xl66"]31[/TD]

[TD="class: xl66"]Metric D[/TD]
[TD="class: xl66"]22[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]24[/TD]
[TD="class: xl66"]37[/TD]
[TD="class: xl66"]29[/TD]
[TD="class: xl66"]11[/TD]
[TD="class: xl66"]26[/TD]
[TD="class: xl66"]20[/TD]
[TD="class: xl66"]26
[/TD]

[TD="class: xl66"]Metric E[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"]27[/TD]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]22[/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl66"]53
[/TD]
[TD="class: xl66"]48
[/TD]
[TD="class: xl66"]36[/TD]
[TD="class: xl66"]29
[/TD]

[TD="class: xl65"]Total[/TD]
[TD="class: xl66"]58[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]32[/TD]
[TD="class: xl66"]24[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl65"]Return this cell[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]
[/TD]

</tbody>

The intention is to be able to pull the total for an agent found in "return this Cell" when an Agent 2 is selected on another sheet.

The only way I can think of finding this result is to have an conditional array nested within Index Match set to first search for the cell address of "agent 2" then search increasing row numbers until "Total" is found. provide that cell address then offset by 6 column indexing the value I need.

I have attempted to use Cell("Address" to get the first part of the array then combine with &":"&"$G$2000" as although i have not been able to get the returned value to be read as an array.

I am unable to use a regular index match with offset due to the differing Metric (A,B,C) values although the metric (1,2,3) are static. Additionally there is a space between agent 2 row and metric A row preventing me from searching for the last non blank value offset from Agent 2.

The goal is to be able to type in an agents name on another Sheet and this formula will pull the data from 200+ agents.

Formulas preferred although Macros can be used.

Thanks in advance for the help. this one has me tapped out.

 

[anglais428]

To pull in agent data, without looking into any crazy formula, could you insert a helper column (e.g. before the first column). So let's assume this new column is column A and your data above is now moved one column across starting in column B. Within column A then put =IF(LEFT(B2,5)="Agent",B2,FALSE). This will give you the Agent name and falses. Copy and paste special then replace all the falses with blanks. Then highlight the range in column A, CTRL + G -> special -> blanks. Then press = and the upward arrow and then confirm with Control + Enter. This will put Agent 2 beside all the agent 2 data and agent 3 beside all the agent 3 data.
Then you could insert another helper column and combine columns B and C - =B1&C1 (so it would say Agent 2Agent2...Agent2Total etc.)
Then in your other sheet do a simple Vlookup for the agent name & total. E.g. in my testing it would be =VLOOKUP(A2&"total",Sheet1!$A$2:$L$12,9,0) - This will pull in the 'return this cell text' if either Agent 2 or Agent 3 is written in cell A2 (of the other sheet).

 

[serequiise]

I appreciate the response. You have a fine idea that would work. The issue is I need the file to be automated in a manner where non excel savvy users can copy from a report, paste in the data tab and get results. Until I get an automated formula I will use this idea and process the reports myself. Thank you, this helps tons.

 

[anglais428]

You could convert the above into a macro. The easiest way is to record your steps first (Developer - Record Macro) then edit the macro accordingly (to make it dynamic rather than fixed).