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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
>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
Joined
Feb 15, 2002
Messages
1,743
=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

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

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,091
Members
414,501
Latest member
mdhaumyu

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
Top