0

I have made multiple attempts with this array formula but have had no luck so far. The solutions that are posted all seem similar but none have worked yet.

Here is my worksheet: https://drive.google.com/file/d/0B2SzIXxoU3dYSkYzZWU1U1dvbVE/view?usp=sharing

As you can see I am trying to pull the names of firms from the RVR tab into an array on the FPP-Vendors tab if they meet two criteria (FPP column has an "X" and result column has a "pass"). So far I can only get the array formula to return the name of Firm A, but it should pull Firm A and Firm C (and skip over Firm B, that does not have a "pass" in the result column). The named ranges in my array formula are listed on the RVR tab.

Grade 'Eh' Bacon
  • 3,773
  • 4
  • 24
  • 46
Michael Talbot
  • 3
  • 4
  • Show an example of one of the answers that you've tried so far. Tell us what the error is when you attempt that solution. Otherwise any answer is just going to be exactly the same as the old ones anyway. – Grade 'Eh' Bacon Aug 19 '15 at 17:06
  • I've been trying this formula on the vendors tab: =IFERROR(INDEX(RespondingFirms,SMALL(IF(AND(RespondingFPP="X", PassFail="PASS"),ROW(RespondingFPP)),ROWS(B$13:B13))),"") where the RVR tab's Column B = "Responding Firms" and Column C = "RespondingFPP" – Michael Talbot Aug 19 '15 at 17:17
  • **Tell us what the error is when you attempt that solution**. To do this, you will need to edit your attempt into your original question and add some discussion about what happened when you tried that. – Grade 'Eh' Bacon Aug 19 '15 at 17:25
  • Noted in the question-- I could only get the array formula to return the name of Firm A (first result only, similar to a vlookup), but needed it to pull Firm A and Firm C (skip over Firm B, it does not have a "pass" in the result column). I was able to get the desired result using the formula below. Thanks for your help. – Michael Talbot Aug 19 '15 at 17:35
  • Possible duplicate of [EXCEL: Copy cells from colum A -> C ONLY IF column B = X, but only using 1 column](https://stackoverflow.com/questions/44785202/excel-copy-cells-from-colum-a-c-only-if-column-b-x-but-only-using-1-colum) – theforestecologist Jun 27 '17 at 17:02

2 Answers2

1

Adjust your ranges as necessary and enter with CTRL + SHIFT + ENTER:

=IFERROR(INDEX($B$2:$B$4,SMALL(IF($C$2:$C$4="X",IF($D$2:$D$4="Pass",ROW($A$2:$A$4)-ROW($A$2)+1)),ROWS($A$2:A2))),"")

enter image description here

rwilson
  • 2,105
  • 11
  • 18
  • That helped! Took a little guess and check work to get the ranges right but solved and working now. Thank you! I ended up adding another named range (RespondingNo which is the number in column A on the RVR tab) and using the formula =IFERROR(INDEX(RespondingFirms,SMALL(IF(RespondingFPP="X",IF(PassFail="Pass",ROW(RespondingNo)-ROW(RVR!$A$7)+1)),ROWS($A$13:A13))),"") works like a charm – Michael Talbot Aug 19 '15 at 17:32
0

I ended up adding another named range (called RespondingNo, which is the number in column A on the RVR tab) and using the formula 

=IFERROR(INDEX(RespondingFirms,SMALL(IF(RespondingFPP="X",IF(PassFail="Pass",ROW‌​(RespondingNo)-ROW(RVR!$A$7)+1)),ROWS($A$13:A13))),"")
Grade 'Eh' Bacon
  • 3,773
  • 4
  • 24
  • 46
Michael Talbot
  • 3
  • 4