where to put isna in this formula please

[curiosity]

G'day all,

ok i have this formula

=INDEX($C$2:$C$425,MATCH(D2,IF($B$2:$B$425=E2,$A$2:$A$425),0))


which works fine, I only have one problem I cannot for the life of me work out where or how to insert the IsNA to give me a 0 value if no match found so that I can sum my results-

Any and all help appreciated..


Steve

 

[acw]

Steve

Try

=IF(ISNA(MATCH(D2,IF($B$2:$B$425=E2,$A$2:$A$425),0)),0,INDEX($C$2:$C$425,MATCH(D2,IF($B$2:$B$425=E2,$A$2:$A$425),0)))


Tony

 

[curiosity]

yes yes yes yes and yes


Tony thank you very much that worked like an absolute gem !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Cant thank you enough - and wow less than 5 minutes for the solution...


Stoked and greatful...


Steve

 

[acw]

Steve

What sort of data is in column C? If it is numeric, then you could use the SUMPRODUCT function which would evaluate to 0 if there was no match.


Tony