Robb G
Robb G

Reputation: 11

excel - if and statement inside an iferror with index small row nest

I am new with Excel and everything I have learned is through researching forums.

My goal is to build a form letter that pulls data from my tables and populates relevant information for several projects - so far everything is working fine

I have the following working formula:

=IFERROR(INDEX(LI_Input[LI],SMALL(IF($B$4=LI_Input[Grab File],ROW(LI_Input[Grab File])-MIN(ROW(LI_Input[Grab File]))+1,""),ROW(A2))),"")

This pulls all the phone numbers attached to a sub project($B$4)

What I would like to do is add an AND statement to add a condition that pulls only phone numbers that belong to a phone company (Verison, Sprint, etc... located in cell b2) along with the

This is one of the variation I have tried but no results display

=IFERROR(INDEX(LI_Input[LI],SMALL(IF(AND($B$4=LI_Input[Grab File],B2=LI_Input[Provider LI 1]),ROW(LI_Input[Grab File])-MIN(ROW(LI_Input[Grab File])))+1,""),ROW(A1)),"")

Any suggestions?

Upvotes: 1

Views: 349

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

Array formula do not work with AND or OR. Use * or + respectively as replacements.

=IFERROR(INDEX(LI_Input[LI],SMALL(IF(($B$4=LI_Input[Grab File])*(B2=LI_Input[Provider LI 1]),ROW(LI_Input[Grab File])-MIN(ROW(LI_Input[Grab File])))+1,""),ROW(A1)),"")

Being an array formula it must be confirmed with Ctrl-shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put [] around the formula.

Upvotes: 1

Related Questions