# 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 do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Mark W.

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

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

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

#### Mark W.

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

#### texasalynn

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

#### IML

##### MrExcel MVP
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
4
Views
53
Replies
7
Views
166
Replies
4
Views
49
Replies
4
Views
239
Replies
20
Views
213

1,171,534
Messages
5,876,060
Members
433,172
Latest member

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