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

#### socool111

##### Board Regular
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

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

#### socool111

##### Board Regular
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:

##### MrExcel MVP
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

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

#### socool111

##### Board Regular
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

##### Board Regular
....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.

##### MrExcel MVP
....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.

Replies
0
Views
736
Replies
13
Views
579
Replies
6
Views
629
Replies
1
Views
445
Replies
4
Views
239

1,190,677
Messages
5,982,215
Members
439,769
Latest member
trungminh2802

### 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?

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