Match and N/A

[texasalynn]

I have the following formula that works fine when the match exists, but when there is no match I get #N/A.

=IF(MATCH(A10,'JE data'!$A$2:$A$56,0),VLOOKUP(A10,'Revsum data'!$A$2:$K$118,11,FALSE),0)

I need to get rid of this. I tried putting in a ISNA statement in front of the match, but then I get the vlookup information. If the match does not exist I need to return 0.

I hope I have explained this correctly.

Thanks
texasalynn

 

[Mark W.]

>I need to get rid of this.

Why?

And, what is the match value that results in #N/A?
This message was edited by Mark W. on 2002-10-15 11:13

 

[IML]

=IF(MATCH(A10,'JE data'!$A$2:$A$56,0),VLOOKUP(A10,'Revsum data'!$A$2:$K$118,11,FALSE),0)

If I understand you correctly, the match test should be on the first column of you lookup data, which would appear to be:

=IF(MATCH(A10,'Revsum data'!$a$2:$a$118,0),VLOOKUP(A10,'Revsum data'!$A$2:$K$118,11,FALSE),0)


But as the match will N/A out in the same fashion, you can wrap it in an isnumber statement, such as
=IF(isnumber(MATCH(A10,'Revsum data'!$a$2:$a$118,0)),VLOOKUP(A10,'Revsum data'!$A$2:$K$118,11,FALSE),0)

I hope my cutting and pasting worked, not tested on a worksheet.

 

[Aladin Akyurek]

On 2002-10-15 11:06, texasalynn wrote:
I have the following formula that works fine when the match exists, but when there is no match I get #N/A.

=IF(MATCH(A10,'JE data'!$A$2:$A$56,0),VLOOKUP(A10,'Revsum data'!$A$2:$K$118,11,FALSE),0)

I need to get rid of this. I tried putting in a ISNA statement in front of the match, but then I get the vlookup information. If the match does not exist I need to return 0.

I hope I have explained this correctly.

Thanks
texasalynn

Alynn,

Change

=IF(MATCH(A10,'JE data'!$A$2:$A$56,0),VLOOKUP(A10,'Revsum data'!$A$2:$K$118,11,FALSE),0)

to

=IF(ISNUMBER(MATCH(A10,'JE data'!$A$2:$A$56,0)),IF(ISNUMBER(MATCH(A10,''Revsum data'!$A$2:$A$118,0)),VLOOKUP(A10,'Revsum data'!$A$2:$K$118,11,0),0),0)

BTW, why do you need to look at 'JE data'!$A$2:$A$56 ?

Aladin

 

[Mark W.]

texasalynn, before you invest in any solution take a moment to consider the root cause and some alternatives.

 

[texasalynn]

BTW, why do you need to look at 'JE data'!$A$2:$A$56 ?

Aladin

That is what determines if I want the data in that column. If the match does not exist, then that column should not have an amount.

Your formula worked as usual. Thank you!

HTH
texasalynn

 

[Mark W.]

There's no doubt that Aladin's and IML's formulas work. The only question is... "Is there a simplier solution"? However, that can't be answered without more information about the nature of your problem.

 

[IML]

This goes back to Mark's root cause question, but if you do need to ensure your look up exists on both sheets, you can condense aladin's a bit to:

=IF(ISNUMBER(MATCH(A10,'je data'!$A$2:$A$56,0)*MATCH(A10,'revsum data'!$A$2:$A$118,0)),VLOOKUP(A10,'revsum data'!$A$2:$K$118,11,0),0)

Now I'll wait to get struck be lightening for playing in the gods' sand box. Good luck.