Forum Discussion
merging 2 spreadsheets with different rows
=IFERROR(INDEX($H$3:$H$12,MATCH(1,(A3=$F$3:$F$12)*(B3=$G$3:$G$12),0)),"")You can apply this formula for the sample data. The formula can be adapted to the actual size of the data and additional columns don't interfere with the formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
Can you attach screenshots of your worksheets (which show all relevant information) or your file both without sensitive data? This way i can make a suggestion on how to adapt the formula to your actual database.
As you can see the client name, client number, task type name are the same on both spreadsheets but the first spreadsheets has a lot of additional columns. The complexity level is the same but that is what needs to be inputted from the second spreadsheet into the first spreadsheet. Unfortunately, the first spreadsheet has additional clients listed that are new this year so it is not a simple copy and paste matter. The client number and task type name have to match from the first and second spreadsheet and then add the complexity level. I hope this makes sense. I am not a beginner at Excel but I also am not advanced enough to know how to do this and where to put the formulas to make this occur.
=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.