Aug 09 2022 02:08 PM
Hello,
In building on other recent posts for help (https://techcommunity.microsoft.com/t5/excel/help-with-syntax-functions-to-select-cells-based-on-cri...), 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))
Aug 10 2022 02:17 PM
Solution= 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)),"")
Aug 11 2022 11:53 AM
Aug 11 2022 12:10 PM
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" )
Aug 11 2022 12:28 PM
Aug 10 2022 02:17 PM
Solution= 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)),"")