Using INDEX & MATCH to get multiple values

[kevin994]

I am using the below formula as an array to find values from the sheet 'paste' in column T which has numeric values by looking up two sets of values in two colums and ten returning the matching result.

{=IFERROR(INDEX($C$1:$C$4,MATCH($D$2&$E$2,$A$1:$A$4&$B$1:$B$4,0)),"")}

This works fine but i wish for any values that have the same lookup criteria to be added together so for example

A B C d2=1 e2=1
1 1 blank
1 3 8
1 1 5
1 1 3

So in cell d1 I have the value '1' and in cell E2 I have the value '1' also so the MATCH will find the first rown and return a blank but i need it to also find row 3 and row 4 add the results together (blank + 5 +3) should return 8.

Many thanks
Kevin

[José Augusto]

Hi
Try this

Please Login or Register  to view this content.

[kevin994]

Thats great thanks.

Just one more thing on this if the value is a blank how does it show as blank instead of a zero. but if there is a zero then to show the zero

so for instance

A B C d2=1 e2=1
1 1 blank
1 3 0
1 1 5
1 1 3

so if d2=1 and e2=1 then blank cell is returned
but if d2=1 and e2=3 then a '0' is returned

Thanks

[zbor]

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

http://www.excelforum.com/excel-form...le-values.html

Thread Closed.