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

socool111

Board Regular
Joined
Jul 16, 2014
Messages
51
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)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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?
 
Last edited:
Upvote 0
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...
 
Upvote 0
you did, i typed that up before i saw your response, give me a sec to redo it to your specifications
 
Upvote 0
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
 
Upvote 0
....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.
 
Upvote 0
....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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top