x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 255

# index match to add 3times and return sum value

Hi Experts excel 2007

I have the following index match formula
=iferror(index('pivot '! \$b\$3:\$az\$14,Match(\$A\$154,'pivot'!,0),Match(D152,'pivot'! \$b\$2:\$az\$2))+
(index('pivot '! \$b\$3:\$az\$14,Match(\$A\$155,'pivot'!,0),Match(D152,'pivot'! \$b\$2:\$az\$2))+ (index('pivot '! \$b\$3:\$az\$14,Match(\$A\$156,'pivot'!,0),Match(D152,'pivot'! \$b\$2:\$az\$2)),"")
0
route217
• 5
• 4
1 Solution

Commented:
What is the question ?
gowflow
0

Author Commented:
Apologies. .experts and gowflow

the question is how do I make the above formula work and add up.
0

EngineerCommented:
Try

=IFERROR(INDEX('Pivot '!\$B\$3:\$AZ\$14,MATCH(\$A\$154,'Pivot '!\$B\$3:\$B\$14,0),MATCH(D152,'Pivot '!\$B\$2:\$AZ\$2))+
INDEX('Pivot '!\$B\$3:\$AZ\$14,MATCH(\$A\$155,'Pivot '!\$B\$3:\$B\$14,0),MATCH(D152,'Pivot '!\$B\$2:\$AZ\$2))+ INDEX('Pivot '!\$B\$3:\$AZ\$14,MATCH(\$A\$156,'Pivot '!\$B\$3:\$B\$14,0),MATCH(D152,'Pivot '!\$B\$2:\$AZ\$2)),"")
0

Author Commented:
Ssaqib

what's the diff between ur formula and mine. .
0

EngineerCommented:
=iferror(index('pivot '! \$b\$3:\$az\$14,Match(\$A\$154,'pivot'!,0),Match(D152,'pivot'! \$b\$2:\$az\$2))+
(index('pivot '! \$b\$3:\$az\$14,Match(\$A\$155,'pivot'!,0),Match(D152,'pivot'! \$b\$2:\$az\$2))+ (index('pivot '! \$b\$3:\$az\$14,Match(\$A\$156,'pivot'!,0),Match(D152,'pivot'! \$b\$2:\$az\$2)),"")

=IFERROR(INDEX('Pivot '!\$B\$3:\$AZ\$14,MATCH(\$A\$154,'Pivot '!\$B\$3:\$B\$14,0),MATCH(D152,'Pivot '!\$B\$2:\$AZ\$2))+
INDEX('Pivot '!\$B\$3:\$AZ\$14,MATCH(\$A\$155,'Pivot '!\$B\$3:\$B\$14,0),MATCH(D152,'Pivot '!\$B\$2:\$AZ\$2))+ INDEX('Pivot '!\$B\$3:\$AZ\$14,MATCH(\$A\$156,'Pivot '!\$B\$3:\$B\$14,0),MATCH(D152,'Pivot '!\$B\$2:\$AZ\$2)),"")
0

Author Commented:
Ok tired that still not working. ..
0

EngineerCommented:
"not working" does not tell me what the problem is. You need to explain "How" it is not working.

Is it not accepted by excel
Is it giving an error value
Is it giving a value but wrong

Unless you give details or a sample file for testing it is very difficult to help you.
0

Author Commented:
Saqib....

Its returning back a zero value when u manually check the data the answer is 2..
0

Author Commented:
There is a #n/a error on the last index match formula. ..
0

EngineerCommented:
Can you upload a sample sheet?

I entered the range

\$B\$3:\$B\$14

based on my judgement which could have been wrong. Either you verify this range on your own or send us something which we can look at.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.