INDEX/MATCH help

[KLassman]

I just learned about the INDEX/MATCH function while searching some of
the Excel tip pages - and I think I can make good use of it. What I
want to do is this:

worksheet 1 - is the format of our Income Statement

worksheet 2 (titled TB) is our Trial Balance.

I want worksheet 1 to read TB (worksheet 2) and pick up the YTD amount
for each account #.

I tested this out and my formula is working fine. However, sometimes
an account # on worksheet 1 doesn't appear on Worksheet 2 because there
wasn't any activity. In this case I get a $N/A.

I need to edit my formula so that if there is no "match" Excel will
enter -- (two dashes) or a zero . . . not the $N/A I am getting now.

The formula I'm using is this:

=INDEX(TB!$A$7:$E$111,MATCH(A7,TB!$A$7:$A$111,0),5)

What can I include in the formula so I won't get $N/As but -- when
there is no match?

Thanks in advance for any suggestions.

 

[Tom Ogilvy]

=if(isNa(match(A7,TB!$A$7:$A$111,0)),"--",INDEX(TB!$A$7:$E$111,MATCH(A7,TB!$
A$7:$A$111,0),5))

 

[KLassman]

Thanks Tom! Can I ask one more question? I'd like the -- to be
interpreted as zero . . . so it can be used in calculations in the
next column. Is that possible?

What you created is an IF function, right? What does the isNa stand
for? If I knew that, I think I can interpret the rest of it.
Thanks.

 

[Tom Ogilvy]

=if(isNa(match(A7,TB!$A$7:$A$111,0)),0,INDEX(TB!$A$7:$E$111,MATCH(A7,TB!$
A$7:$A$111,0),5))

ISNA returns true if the argument returns a #N/A value. Otherwise it return
false.

Match Returns #N/A when it does not find a match.

Yes, it is an If function.

 

[KLassman]

Thank you very much!! Appreciate the help and so will my boss!

 

[Jmo]

Don't forget the curly brackets around the INDEX MATCH
formula - (highlight formula and hit ctrl/shift/enter)

 

[Peo Sjoblom]

No need for that in this case

 

[aitor solozabal]

There is a special function of group ISERROR controlling
the N/A error, you can nest the two functions. Read Help.

=iserror(INDEX(TB!$A$7:$E$111,MATCH(A7,TB!
$A$7:$A$111,0),5);"NOTHING";=INDEX(TB!$A$7:$E$111,MATCH
(A7,TB!$A$7:$A$111,0),5))