After entering the date and selecting a complaint from dropdown box in cell C, the lookup formulas in cells D and F check a table on 'Stuff' sheet which contains upto two questions asked for specific complaints and date ranges. If a match is found, the questions are returned to Sheet1 cells D and F respectively.
There can be multiple date ranges for a single complaint point. Sometimes one question is asked, sometimes two. Only one date range will apply though.
The lookup function as it stands works fine, i just want to surpress #VALUE! when no match is found, and also perhaps surpress the 0 when cell E on sheet Stuff is empty (this implies only one question is asked - the formula in Sheet1 cell D will return the first question, but as no second question, the formula in Sheet1 cell E will return '0').
The sample data here contains examples of three outcomes:
a) (ROW 1) - Lookup successful. One matching question found. No second question, so '0' is returned. I want it to be blank instead of 0.
b) (ROW 2) - Lookup unsuccessful. No matches found. Both cells return #VALUE!. I want it to be blank instead of #VALUE!.
c) (ROW 3) - Lookup successful. Two matching questions found.