index/match problem

Hi,

I'm trying to do an index match formula to look up a name and number in a pivot table but the two values I'm looking up and concatenate are in the same row:

{=INDEX(A:B,MATCH(H5&I4,A:A&A:A,0),2)}

So in this formula, I'm trying to find a total sum in a two column pivot table that matches two criteria in H5 and I4 and searching for those two values in the same column of the pivot table (column A).

It's giving me an #N/A value.

Any suggestions?

In the attached example, Im trying to get the volume count from the first pivot table for Family Practice for Week 13 (11) and get this value in cell I5. Let me know if you have any questions about this. Thank you!

In the attached example, Im trying to get the volume count from the first pivot table for Family Practice for Week 13 (11) and get this value in cell I5. Let me know if you have any questions about this. Thank you!

Hi,
Attached file for your reference,


P/S: I used an array formula confirmed with Ctrl+shift+Enter!
Hope this helps!

Hi Dave,

Thanks for your reply. I created a new source table array using "=C6" etc. and used that and now I get a value error

Wow, thank you so much. Would it be possible to explain what you did in laymen's terms? If not, no big deal. I appreciate your help.

I put this formula in I5:

Formula:

Please Login or Register  to view this content.

Note that the second and third range is offset by one cell from the first range. In other columns it is offset by 2, 3, and 4 cells. Those formulae can be copied down to row 9, and in I10 I have this formula:

Formula:

Please Login or Register  to view this content.

which is copied across.

Hope this helps.

Pete

Thank you, Pete_UK!!!

One question: I tried extending the rows for column A and B down to 500 because the pivot table length could end up being longer in different iterations of this report. However, when I changed it down to include blank rows before the pivot table, it gives me an error. Any suggestions?

Thanks again!

Maybe you didn't do it right. Highlight cells I5 to L5 on the original file, then CTRL-H, and:

Find what $25
Replace with $55
Click Replace All

That should be 12 replacements, and now the ranges go up to 550 rows. Copy those formulae down to row 9.

Hope this helps.

Pete

Hi Pete,

Sorry to bug you again. This might not be possible given the constraints of the sumproduct but the formula needs to accomodate changes in the department and week number availability for every reporting period. So for example, if I replace Family Practice with Parking in the table on the right, it needs to populate with 1 for week 13, 4 for week 15, and 2 for week 16 which corresponds to the counts for parking in the pivot table. Like I said, I'm not sure if this is possible, but I appreciate your help anyways. Thanks!

Hi Dave,

I was able to create a new array based on the pivot table, and changed the equation to match this new array:

=INDEX(C5:D500,MATCH(J5&K4,$C$5:$C$500&$C$5:$C$500,0),2)

I am getting a value error. Do you have any suggestions?

Hi PMA,

I was about to suggest you try something before getting too far down a route that might have problems...

Go back to your source data table and add in some records that will result in a new category and a few rows to populate your pivot table, then refresh the pivot table and see if the injected extra category + rows screws up your results in the MATCH logic by changing the coordinates of the referenced values.

If it does, you have a major challenge - you're going to have to investigate the GETPIVOTDATA option (with all that that entails).

I have not been able to find any reference to using Array Formulae to reference the data values in a pivot table but my guess is that Array Formulae only work with "genuine" Excel cell ranges and not mapped data from pivot tables. To illustrate this, generate a simple pivot table with 5 or 6 rows and add some formulae into a row located 2 rows below the base of the pivot table. Then add some data to the source table and refresh and the expanded pivot table will not result in the formula row being shifted down, it will just be overwritten by the mapped pivot table.

Sorry mate...

Dave

By injecting nested IFERROR tests each with unique error codes, it appears that the INDEX is failing with a value error but the MATCH is not failing, but is the cause of the error with INDEX. All I can think of is that the MATCH is returning a value that pushes the INDEX out of the range - but I can't be 100% sure.

Sorry...

I actually was able to retrieve this data by doing a sumif function in the raw data source. Thanks!