# where to put isna in this formula please

#### curiosity

##### New Member
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

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### acw

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

Replies
8
Views
179
Replies
3
Views
110
Replies
4
Views
26
Replies
3
Views
58
Replies
7
Views
90

Threads
1,114,057
Messages
5,545,755
Members
410,704
Latest member
Cobber2008