# Error in semi-complex formula

#### Hanz77

##### Board Regular
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.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Hanz77

##### Board Regular
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.

Replies
3
Views
351
Replies
0
Views
183
Replies
1
Views
575
Replies
12
Views
525
Replies
2
Views
495

1,181,730
Messages
5,931,718
Members
436,800
Latest member
abowalid98

### 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.

### Which adblocker are you using?

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

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