Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-775378%22%20slang%3D%22en-US%22%3Ehow%20to%20combine%20data%20from%20two%20sheets%20using%20index%2Fsmall%20in%20an%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775378%22%20slang%3D%22en-US%22%3E%3CP%3EI%20use%20the%20following%20formula%20to%20look%20in%20the%20%22final%22%20tab%20and%20return%20%22Quality%20Control%22%20data%20as%20described%20in%20the%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(final!%24G%242%3A%24G%2420000%2CSMALL(IF(%22Quality%20Control%22%3Dfinal!%24B%242%3A%24B%2420000%2CROW(final!%24G%242%3A%24G%2420000)-1%2C%22%22)%2CROW()-1))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20I%20have%20%22Quality%20control%22%20data%20in%20another%20tab%20as%20well!%20Is%20there%20any%20way%20to%20have%20excel%20look%20in%20%3CEM%3Etwo%3C%2FEM%3Edifferent%20tabs%20and%20combine%20the%20data%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-775378%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776058%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20combine%20data%20from%20two%20sheets%20using%20index%2Fsmall%20in%20an%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776058%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Greg%3C%2FP%3E%3CP%3EWould%20be%20great%20if%20you%20share%20your%20file%20.%26nbsp%3B%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776077%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20combine%20data%20from%20two%20sheets%20using%20index%2Fsmall%20in%20an%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776077%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20extract%20data%20from%20two%20ranges%20you%20may%20do%20that%20sequentially%20-%20when%20error%20appear%20on%20first%20range%20start%20selecting%20data%20from%20the%20second%20one.%20Just%20repeat%20you%20formula%20in%20place%20of%20%22%22%20for%20the%20first%20IFERROR%20adding%20to%20counting%20ROW()%20number%20of%20records%20found%20in%20first%20range.%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20318px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124682iBF30312D58A1D1D6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20in%20G2%20will%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-java%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20INDEX(%24B%242%3A%24B%2412%2C%0A%20%20%20%20SMALL(%0A%20%20%20%20%20%20IF(%22QC%22%3D%24A%242%3A%24A%2412%2C%0A%20%20%20%20%20%20%20%20ROW(%24B%242%3A%24B%2412)-1%2C%0A%20%20%20%20%20%20%20%20%22%22%0A%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20ROW()-1)%0A%20%20%20%20)%2C%0A%20%20IFERROR(%0A%20%20%20%20INDEX(%24E%242%3A%24E%2412%2C%0A%20%20%20%20%20%20SMALL(%0A%20%20%20%20%20%20%20%20IF(%22QC%22%3D%24D%242%3A%24D%2412%2C%0A%20%20%20%20%20%20%20%20%20%20ROW(%24E%242%3A%24E%2412)-1%2C%0A%20%20%20%20%20%20%20%20%20%20%22%22%0A%20%20%20%20%20%20%20%20)%2CROW()-COUNTIF(%24A%242%3A%24A%2412%2C%22QC%22)-1)%0A%20%20%20)%2C%0A%20%20%22%22)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down.%3C%2FP%3E%0A%3CP%3ESample%20file%20is%20attached.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776120%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20combine%20data%20from%20two%20sheets%20using%20index%2Fsmall%20in%20an%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776120%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Greg%2C%3C%2FP%3E%3CP%3EYou%20may%20also%20consider%20using%20%3CSTRONG%3EPower%20Query%3C%2FSTRONG%3Eand%20just%20refresh%20when%20new%20data%20is%20added%20to%20the%20source.%3C%2FP%3E%3CP%3EI%20attached%20a%20sample%20file%20where%20I%20created%20a%20similar%20situation.%3C%2FP%3E%3CUL%3E%3CLI%3EConvert%20each%20source%20to%20a%20Table%20(CTRL%20%2B%20T)%3C%2FLI%3E%3CLI%3EData%20Tab%20%26gt%3B%26gt%3B%20From%20Table%20%26gt%3B%26gt%3B%20Close%20and%20Load%20To%20%26gt%3B%26gt%3B%20Only%20Create%20a%20Connection%3C%2FLI%3E%3CLI%3ERepeat%20for%20the%20second%20Table%20(or%20for%20any%20number%20of%20Tables)%3C%2FLI%3E%3CLI%3EData%20Tab%20%26gt%3B%26gt%3B%20Combine%20Query%20%26gt%3B%26gt%3B%20Append%20%26gt%3B%26gt%3B%20Select%20the%20Previous%20Tables%3C%2FLI%3E%3CLI%3EFilter%20the%20Column%20to%20Your%20Criteria%3C%2FLI%3E%3CLI%3ERemove%20the%20Filtered%20Column%3C%2FLI%3E%3CLI%3EClose%20and%20Load%20To%20%26gt%3B%26gt%3B%20Select%20a%20Destination%20and%20we%20are%20done%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EIn%20another%20Scenario%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20can%20create%20a%20third%20table%20for%20All%20criteria%20(Data%20Validation)%3C%2FP%3E%3CP%3EConvert%20the%20above%20Query%20to%20a%20Function%3C%2FP%3E%3CP%3EUse%20Table%203%20(Criteria)%20as%20a%20variable%20input%20to%20the%20M%20Query%20Function%3C%2FP%3E%3CP%3EClose%20and%20Load%26gt%3B%26gt%3B%20To%20destination%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20Query%20enables%20you%20to%20combine%20%3CSTRONG%3Emore%20than%202%20data%20sources%3C%2FSTRONG%3Every%20smoothly%3C%2FP%3E%3CP%3EUsing%20a%20Query%20Function%20makes%20it%20full%20dynamic%20(with%20Refresh)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EIf%20you%20want%20to%20take%20it%20to%20a%20whole%20next%20level%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EAdding%20a%20little%20code%20to%20the%20Change%20event%20of%20the%20cell%20having%20Data%20Validation%2C%20Can%20automatically%20refresh%20the%20Query%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20Helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
gms4b
Contributor

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$20000,ROW(final!$G$2:$G$20000)-1,""),ROW()-1)),"")

 

The problem is that I have "Quality control" data in another tab as well! Is there any way to have excel look in two different tabs and combine the data?

 

Thanks,


Greg

 

3 Replies

Hi Greg

Would be great if you share your file .  Thanks

@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

image.png

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.

 

@gms4b 

Hi Greg,

You may also consider using Power Query and just refresh when new data is added to the source.

I attached a sample file where I created a similar situation.

  • Convert each source to a Table (CTRL + T)
  • Data Tab >> From Table >> Close and Load To >> Only Create a Connection
  • Repeat for the second Table (or for any number of Tables)
  • Data Tab >> Combine Query >> Append >> Select the Previous Tables
  • Filter the Column to Your Criteria
  • Remove the Filtered Column
  • Close and Load To >> Select a Destination and we are done

 

In another Scenario

I can create a third table for All criteria (Data Validation)

Convert the above Query to a Function

Use Table 3 (Criteria) as a variable input to the M Query Function

Close and Load>> To destination

 

Using Query enables you to combine more than 2 data sources very smoothly

Using a Query Function makes it full dynamic (with Refresh)

 

If you want to take it to a whole next level:

Adding a little code to the Change event of the cell having Data Validation, Can automatically refresh the Query

 

Hope that Helps

Nabil Mourad

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies