Hi, I wonder whether someone may be able to help me please.
From this site Lookup with multiple criteria and display multiple search results using excel formula, part 4 | Get Digital Help - Microsoft Excel resource I've put together this formula, which works as expected:
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--> [FONT="]=SMALL(IF(ISNUMBER(SEARCH(Search_customer, Customer)*SEARCH(Search_cust_name, Cust_Name)*SEARCH(Search_Appt_date, Appt_date)*SEARCH(Search_Appt_time, Appt_time)*SEARCH(Search_venue, Venue)*SEARCH(Search_Coordinator, Coordinator)*SEARCH(Search_Assistant, Assistant)), ROW(Customer)-MIN(ROW(Customer))+1), ROWS(B$24:$B26))[/FONT]
The problem I'm having is where there is no data to return, if this is the case then the cell read #NUM!. I understand why thbis happens but I'm not sure how to overcome it.
Could somene perhaps offer a little guidance please, so instead of the cell showing the #NUM error, the cell is blank.
<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->
Many thanks and kind regards
From this site Lookup with multiple criteria and display multiple search results using excel formula, part 4 | Get Digital Help - Microsoft Excel resource I've put together this formula, which works as expected:
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--> [FONT="]=SMALL(IF(ISNUMBER(SEARCH(Search_customer, Customer)*SEARCH(Search_cust_name, Cust_Name)*SEARCH(Search_Appt_date, Appt_date)*SEARCH(Search_Appt_time, Appt_time)*SEARCH(Search_venue, Venue)*SEARCH(Search_Coordinator, Coordinator)*SEARCH(Search_Assistant, Assistant)), ROW(Customer)-MIN(ROW(Customer))+1), ROWS(B$24:$B26))[/FONT]
The problem I'm having is where there is no data to return, if this is the case then the cell read #NUM!. I understand why thbis happens but I'm not sure how to overcome it.
Could somene perhaps offer a little guidance please, so instead of the cell showing the #NUM error, the cell is blank.
<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->
Many thanks and kind regards
