Forum Discussion

gms4b's avatar
gms4b
Brass Contributor
Jul 26, 2019

how to combine data from two sheets using index/small in an array

I use the following formula to look in the "final" tab and return "Quality Control" data as described in the formula.    =IFERROR(INDEX(final!$G$2:$G$20000,SMALL(IF("Quality Control"=final!$B$2:$B$...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 27, 2019

gms4b 

 

To extract data from two ranges you may do that sequentially - when error appear on first range start selecting data from the second one. Just repeat you formula in place of "" for the first IFERROR adding to counting ROW() number of records found in first range.

For such sample

formula in G2 will be

=IFERROR(
  INDEX($B$2:$B$12,
    SMALL(
      IF("QC"=$A$2:$A$12,
        ROW($B$2:$B$12)-1,
        ""
      ),
      ROW()-1)
    ),
  IFERROR(
    INDEX($E$2:$E$12,
      SMALL(
        IF("QC"=$D$2:$D$12,
          ROW($E$2:$E$12)-1,
          ""
        ),ROW()-COUNTIF($A$2:$A$12,"QC")-1)
   ),
  "")
)

and drag it down.

Sample file is attached.

 

Resources