Match and N/A

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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
 
Upvote 0
=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.
 
Upvote 0
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
 
Upvote 0
texasalynn, before you invest in any solution take a moment to consider the root cause and some alternatives.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top