# 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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### 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
7
Views
743
Replies
1
Views
650
Replies
1
Views
260
Replies
3
Views
316
Replies
3
Views
233

1,171,065
Messages
5,873,580
Members
432,985
Latest member
leahw

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