Forum Discussion
copy values from one excel sheet to another by matching multiple columns
= 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)),"")
= 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" )- Awesome! Yes that worked! Thanks!
