Help with INDEX and MATCH and MID

[Sean15]

Hi:

I am trying to compare data from two pivot tables.

Formula below in E4 of worksheet pivot table 2 is trying to returning to return required values from worksheet pivot table 1 but Excel is returning #VALUE

=INDEX('pivot table1'!$B$6:$B$49,MATCH(B5,MID('pivot table1'!$A$6:$A$49,5,7)*1),0)

Could you help please?

Regards,

Sean

 

[Twollaston]

Hi:

I am trying to compare data from two pivot tables.

Formula below in E4 of worksheet pivot table 2 is trying to returning to return required values from worksheet pivot table 1 but Excel is returning #VALUE

=INDEX('pivot table1'!$B$6:$B$49,MATCH(B5,MID('pivot table1'!$A$6:$A$49,5,7)*1),0)

Could you help please?

Regards,

Sean

Change the 0 at the end of the formula to 1
or delete the 0 and the comma before it

 

[Sean15]

Hi:

Suggestions in post #2 still returns #VALUE .

Example of values in column B of pivot table1: 123-4562566-220-----

Example of values in column B of pivot table2: 4562566


Regards,

Sean

 

[Twollaston]

Hi:

Suggestions in post #2 still returns #VALUE .

Example of values in column B of pivot table1: 123-4562566-220-----


Example of values in column B of pivot table2: 4562566


Regards,

Sean

without seeing the full pivot table and data it's going to be hard for me to diagnose
you can try to double click into the cell with your formula and highlight this with the cursor : MATCH(B5,MID('pivot table1'!$A$6:$A$49,5,7)*1) and then press F9 to calculate it, and see what this part of the formula is giving you, is it giving you the row you are looking for in the row labels?

 

[Twollaston]

without seeing the full pivot table and data it's going to be hard for me to diagnose
you can try to double click into the cell with your formula and highlight this with the cursor : MATCH(B5,MID('pivot table1'!$A$6:$A$49,5,7)*1) and then press F9 to calculate it, and see what this part of the formula is giving you, is it giving you the row you are looking for in the row labels?

You may have to enter the formula with Ctrl + Shift + Enter, as an array formula. double click in it and hit control + shift + enter

 

[Sean15]

Hi:

Pressing F9 returns:

=INDEX('pivot table1'!$B$6:$B$49,3)


Sean

 

[Twollaston]

Hi:

Pressing F9 returns:

=INDEX('pivot table1'!$B$6:$B$49,3)


Sean

make sure to undo after calculating, cause it will change your formula

Okay 3, is that the row you want? the 3rd item in your row headers?


double click in the formula and hit Ctrl + Shift + Enter, see if that makes it work

 

[Sean15]

You may have to enter the formula with Ctrl + Shift + Enter, as an array formula. double click in it and hit control + shift + enter

Yep, the array works. Thanks very much.

But, could this be done without an array formula?

Sean

 

[Twollaston]

Yep, the array works. Thanks very much.

But, could this be done without an array formula?

Sean

You you can create a column next to your 2nd pivot table, to return the values for each item in pivot table 1, you want to check them against each other right?
And if they aren't in the same order you can use a vlookup to say look at item 1 in pivot table 2, and find this item in pivot table 1, and return the value from pivot table 1. then you can compare the values in each table