# Match and N/A

#### texasalynn

##### Well-known Member
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

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
>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

=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.

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 ?

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

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

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

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.

Replies
8
Views
183
Replies
6
Views
867
Replies
1
Views
110
Replies
7
Views
339
Replies
5
Views
118

1,211,841
Messages
6,104,304
Members
447,901
Latest member
boy3hc2004

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back