Error in semi-complex formula

Hanz77

Board Regular
Joined
Sep 21, 2006
Messages
87
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
Joined
Sep 21, 2006
Messages
87
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top