I think I need an Array Fromula for this Index Match to work...need some help

[socool111]

So I have a data set, in which column A lists clients...followed by a bunch of other columns including date ranges, revenue, employees etc. But I also have a a column (call it "Type")which is either "T","S" or "P".

I then have 3 pivot tables, each grabbing ONLY the client, with a report filter based on the T, S or P. (3 pivot tables, one for each "Type": T, S and P). All the values in the columns next to the pivot table are Index Matches...Indexing the column from the data set, by matching the client. I need to return the index match for the proper client when the Type matches the report filter.

Whats the syntax on the array formula to do it (or if this is done without an array formula..how is it done)

 

[Aladin Akyurek]

Post your pivot for T if small and and provide an example (not a formula) of the calculation you desire.

 

[socool111]

Assume Type is column Z
Indexing Revenue is column B
Client is column A
Sheet1 is where the pivot table is sourced from

I found a very uneloquent solution which is the following:

Index(IF(Sheet1!Z:Z = "T", Sheet1!B:B),MATCH(A4,IF(Sheet1!Z:Z = "T", Sheet1!A:A,0))

is there a more efficient way of solving this



SIDE QUESTION (this applies to ALL if statements, not just this example): If you have an if statement with a lengthy formula (for example the one mentioned above)...is there a way to say IF (long formula is true) then return the long formula (WITHOUT actually having to re-type it / copy+paste the formula)

as an example IF( 5+ 5 > 4, 5+5, FALSE) instead of 5+5 is there a quick thing to put in which references the first 5+5?

 

[Aladin Akyurek]

Assume Type is column Z
Indexing Revenue is column B
Client is column A
Sheet1 is where the pivot table is sourced from

I found a very uneloquent solution which is the following:

Index(IF(Sheet1!Z:Z = "T", Sheet1!B:B),MATCH(A4,IF(Sheet1!Z:Z = "T", Sheet1!A:A,0))

I thought I explicitly asked to resort to words, not to a formula...

 

[socool111]

you did, i typed that up before i saw your response, give me a sec to redo it to your specifications

 

[socool111]

I have the following data set (in Sheet1)

Client Revenue Date Type
AB 1000 1/1/2014 P
AB 1300 2/1/2014 T
CM 3231 3/1/2014 S

I have 3 seperate pivot tables, which ONLY grab Client, with a report filter based on Type (P, T and S). The rest of the columns in Sheet1 (Revenue and Date and Type), is placed next to the Pivot table, so that each pivot tables has 3 columns next to it (so it still looks like Client Revenue Date Type)

I then do an index match based on Client to fill in their revenue and date. In the "T" pivot table, it will grab AB (because AB has a Type of T)..but the index matching will return 1000 and 1/1/2014 (which corresponds to the data for Type "P").

I need a formula which grabs the proper data for the proper type

 

[socool111]

....Although I could jus tbe an idiot...

Is there a way I can just have a full pivot table which pulls in all those values (date and revenue) WITHOUT doing the grouping....So the pivot table would just have the Client in column A, the revenue in column b and date in column C...as opposed to Sum of clients that have the same date.

 

[Aladin Akyurek]

....Although I could jus tbe an idiot...

Is there a way I can just have a full pivot table which pulls in all those values (date and revenue) WITHOUT doing the grouping....So the pivot table would just have the Client in column A, the revenue in column b and date in column C...as opposed to Sum of clients that have the same date.

You could provide a small sample along with the desired output, related to that sample.