Need Help For Formula to Return Zero If No Match

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,860
Is it possible to have the formula in J48 return a blank, if there is no match/
Excel Workbook
J
48#N/A
491097
75%
Excel 2007
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

martindwilson

Well-known Member
Joined
Aug 14, 2009
Messages
824
just put iferror around it

=iferror(MATCH(1,(DRAWS!$R$20:$R$4839=$B48)*(DRAWS!$S$20:$S$4839=J$42),0)-1,"") for blank
or
=iferror(MATCH(1,(DRAWS!$R$20:$R$4839=$B48)*(DRAWS!$S$20:$S$4839=J$42),0)-1,0) for 0
 

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,860
I knew you'll would come thru!! Thank you very much.




just put iferror around it

=iferror(MATCH(1,(DRAWS!$R$20:$R$4839=$B48)*(DRAWS!$S$20:$S$4839=J$42),0)-1,"") for blank
or
=iferror(MATCH(1,(DRAWS!$R$20:$R$4839=$B48)*(DRAWS!$S$20:$S$4839=J$42),0)-1,0) for 0
 

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,860

ADVERTISEMENT

just put iferror around it

=iferror(MATCH(1,(DRAWS!$R$20:$R$4839=$B48)*(DRAWS!$S$20:$S$4839=J$42),0)-1,"") for blank
or
=iferror(MATCH(1,(DRAWS!$R$20:$R$4839=$B48)*(DRAWS!$S$20:$S$4839=J$42),0)-1,0) for 0


Trying to get this formula to return a blank instead of zero.
Excel Workbook
AA
22107
F536 DB
Excel 2007
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Hi.

=IFERROR(1/(1/(SUMPRODUCT(--($J$2:$J$376993=$S22),(--($I$2:$I$376993=AA$21))))),"")

Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,108,989
Messages
5,526,091
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top