Error in semi-complex formula

[Hanz77]

I have a spreadsheet containing three sheets. The first sheet (Sheet1) allows you to enter a customer account number in cell A1. The following two formulas reside in cells A3 and A4 respectively.

{=IF(ISERROR(VLOOKUP(A1,Sheet2!$B$2:$C$1172,2,0)),(VLOOKUP(A1,Sheet3!$B$2:$C$9400,2,0)),INDEX(Sheet2!$B$2:$C$1172,SMALL(IF(Sheet2!$B$2:$C$1172=A1,ROW(Sheet2!$B$2:$C$1172)-ROW(Sheet2!$B$2)+1,ROW(Sheet2!$C$1172)+1),1),2))}

{=IF(ISERROR(VLOOKUP(A1,Sheet2!$B$2:$C$1172,2,0)),(VLOOKUP(A1,Sheet3!$B$2:$C$9400,2,0)),INDEX(Sheet2!$B$2:$C$1172,SMALL(IF(Sheet2!$B$2:$C$1172=A1,ROW(Sheet2!$B$2:$C$1172)-ROW(Sheet2!$B$2)+1,ROW(Sheet2!$C$1172)+1),2),2))}

Essentially, these formulas populate the customer’s home phone number and cell phone number if available.

The first formula works great and the second formula works great if the customer has a cell phone. However, if the customer doesn’t have a cell phone, I get a #REF error. I’ve been trying to modify the second formula to make the cell appear blank opposed to giving the #REF error if no cell phone exists. I’ve tried my normal IF and ISERROR methods without luck…I’m probably placing a parenthesis or comma in the wrong spot to make them work properly.

Any suggestion on making this work is appreciated.

 

[Hanz77]

I am answering my own post in the event someone with a similar issue stumbles across this post.

At the beginning of the post I added a CountIf function to first see if there is more than one occurrence of the same account number. If there is, then the cell phone number is populated otherwise it is left blank. There is probably a more efficient way of doing this, but this worked for me.

{=IF(SUM(IF([Book1.xls]Sheet2!$B$2:$B$1116=I1,1,0))<=1,"",IF(ISERROR(VLOOKUP(I1,[Book1.xls]Sheet2!$B$2:$C$1172,2,0)),(VLOOKUP(I1,[Book1]Sheet3!$B$2:$C$9400,2,0)),INDEX([Book1]Sheet2!$B$2:$C$1172,SMALL(IF([Book1]Sheet2!$B$2:$C$1172=I1,ROW([Book1]Sheet2!$B$2:$C$1172)-ROW([Book1]Sheet2!$B$2)+1,ROW([Book1]Sheet2!$C$1172)+1),2),2)))}

Note: I ended up placing the source data in a second spreadsheet. Because the CountIf formula doesn’t work while the source spreadsheet is closed, I converted the CountIf formula to a “If(Sum(If” formula.