Forum Discussion

Connie64131's avatar
Connie64131
Copper Contributor
Dec 08, 2023

merging 2 spreadsheets with different rows

I have 2 spreadsheets. Spreadsheet 1 has a lot of data (35 columns and 1600+ rows). Spreadsheet 2 does not have as much data (4 columns and 1400+ rows) but one of the columns needs to be merged into ...
excel
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Dec 11, 2023

Connie64131 

=IFERROR(INDEX(Table1[Complexitiy level],MATCH(1,([@[Client Number]]=Table1[Client Number])*([@[Task Type Name]]=Table1[Task Type Name]),0)),"")

 

I've made a small sample file. The formula is in the first row of the "Complexity level" column of the first spreadsheet. The formula is dynamically filled down the "Complexity level" column which is an advantage of working with dynamic tables.

Connie64131's avatar
Connie64131
Copper Contributor
Dec 11, 2023
I have to put my tables into this equation to link it. which part of this equation =IFERROR(INDEX(Table1[Complexitiy level],MATCH(1,([@[Client Number]]=Table1[Client Number])*([@[Task Type Name]]=Table1[Task Type Name]),0)),"") is from table 2 where the info is at? Is it the MATCH?
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Dec 11, 2023

    Connie64131 

    =IFERROR(INDEX(Table1[Complexity level],MATCH(1,([@[Client Number]]=Table1[Client Number])*([@[Task Type Name]]=Table1[Task Type Name]),0)),"")

     

    All information from Table1 (highlighted in red) is from the second spreadsheet from where the complexity level is pulled.

     

    If the name of your table in the second spreadsheet is "SecondSheetTable" then this should work:

     

    =IFERROR(INDEX(SecondSheetTable[Complexity level],MATCH(1,([@[Client Number]]=SecondSheetTable[Client Number])*([@[Task Type Name]]=SecondSheetTable[Task Type Name]),0)),"")

     

    If you don't work with Office 365 or Excel for the web or Excel 2021 you'll have to enter the formula as an arrayformula by pressing ctrl+shift+enter.

    • Connie64131's avatar
      Connie64131
      Copper Contributor
      Dec 11, 2023
      OMG!!! Thank you so so much! You have no idea how much this means to me! I sincerely appreciate your help!!!

Resources