Forum Discussion
copy values from one excel sheet to another by matching multiple columns
Hello,
In building on other recent posts for help (https://techcommunity.microsoft.com/t5/excel/help-with-syntax-functions-to-select-cells-based-on-criteria-of/m-p/3584053#M156384), I now want to update a table based on 2 matching criteria in another table. I've been using the following website (
https://www.exceldemy.com/excel-extract-data-from-table-based-on-multiple-criteria/) for help on using the index/match combination. The first example they use "seems" to be what I need, but I just get error messages. I thought the only problem was that I didn't have the same number of rows in one of the tables, but I did a test by pasting those values into the 97-116 rows and still have an error. After looking at the screenshot example below (and a paste of my syntax), can someone help me fill the "chla" values from the SWL22/columnE file to another file based on the "station/bottle" info when the number of rows isn't the same?
Thanks!
INDEX('[SWL22 Chl-a Data.xlsx]WaterChl ab'!$E$2:$E$257 ,MATCH(1,('[SWL22 Chl-a Data.xlsx]WaterChl ab'!$C$2=$DM$97:$DM$116)*('[SWL22 Chl-a Data.xlsx]WaterChl ab'!$D$2:$D$7=$DO$97:$DO$116),0))
= INDEX('[SWL22 Chl-a Data.xlsx]WaterChl ab'! $E$2:$E$257 ,MATCH(1,('[SWL22 Chl-a Data.xlsx]WaterChl ab'! $C$2:$C$257=DM2)*('[SWL22 Chl-a Data.xlsx]WaterChl ab'! $D$2:$D$257=DO2),0))
I assume the data in the other file starts in row 2 in cells DM2 (SLIP1), DN2 (75), DO2 (1) and the formula should be in cell DP2. From cell DP2 the formula can be filled down.
The formula has to be entered with ctrl+shift+enter if you don't work with Office365 or 2021.
=INDEX($E$2:$E$257,MATCH(1,($C$2:$C$257=DM2)*($D$2:$D$257=DO2),0))I've tried this corresponding formula within one spreadsheet and it works as expected.
If there isn't a match the formula returns #NA (#NV in german Excel). If there shouldn't be an error message the whole formula can be wrapped inside IFERROR.
=IFERROR(INDEX($E$2:$E$257,MATCH(1,($C$2:$C$257=DM2)*($D$2:$D$257=DO2),0)),"")
4 Replies
= INDEX('[SWL22 Chl-a Data.xlsx]WaterChl ab'! $E$2:$E$257 ,MATCH(1,('[SWL22 Chl-a Data.xlsx]WaterChl ab'! $C$2:$C$257=DM2)*('[SWL22 Chl-a Data.xlsx]WaterChl ab'! $D$2:$D$257=DO2),0))
I assume the data in the other file starts in row 2 in cells DM2 (SLIP1), DN2 (75), DO2 (1) and the formula should be in cell DP2. From cell DP2 the formula can be filled down.
The formula has to be entered with ctrl+shift+enter if you don't work with Office365 or 2021.
=INDEX($E$2:$E$257,MATCH(1,($C$2:$C$257=DM2)*($D$2:$D$257=DO2),0))I've tried this corresponding formula within one spreadsheet and it works as expected.
If there isn't a match the formula returns #NA (#NV in german Excel). If there shouldn't be an error message the whole formula can be wrapped inside IFERROR.
=IFERROR(INDEX($E$2:$E$257,MATCH(1,($C$2:$C$257=DM2)*($D$2:$D$257=DO2),0)),"")
- Thank you! And, thanks for the reminder about the cntrl+shift+enter. I seem to be hit or miss at making that work in non-365 Excel (2013). Also, thanks for expanding on the use of iferror...
As a comment, to avoid Ctrl+Shift+Enter on previous versions you may modify OliverScheurich formula like
=IFNA( INDEX($E$2:$E$257,MATCH(1,INDEX( ($C$2:$C$257=DM2)*($D$2:$D$257=DO2), 0 ),0)), "no such" )
