Forum Discussion
How do I pull data from one sheet into another based on multiple matching criteria
=SUMPRODUCT(($B$2:$B$17=$A20)*($A$2:$A$17>=$B20)*($A$2:$A$17<=$C20)*($C$2:$C$17=D$19)*$D$2:$D$17)Does this return the intended result?
OliverScheurich Thanks for the response.
Sno1&2 in Table2 are sample results. data entered manually from table 1. For likewise result in sno3&4 function required.
Serval hundred are there, manually data entry taking days to complete.
TABLE 1:
| Transaction Date Time | Licence Plate No | Plaza Code | Amount(DR) |
| 30-05-2023 16:18 | 123 | 356023 | 165 |
| 30-05-2023 10:43 | 123 | 120001 | 245 |
| 30-05-2023 09:19 | 123 | 120002 | 140 |
| 30-05-2023 08:59 | 123 | 118002 | 145 |
| 30-05-2023 07:24 | 123 | 118001 | 145 |
| 30-05-2023 07:02 | 123 | 116001 | 75 |
| 30-05-2023 06:10 | 123 | 116003 | 265 |
| 29-05-2023 13:06 | 123 | 116003 | 525 |
| 29-05-2023 11:38 | 123 | 116001 | 155 |
| 29-05-2023 11:28 | 123 | 118001 | 295 |
| 29-05-2023 09:54 | 123 | 118002 | 295 |
| 29-05-2023 09:35 | 123 | 120002 | 285 |
| 29-05-2023 08:07 | 123 | 120001 | 245 |
| 28-05-2023 14:53 | 123 | 356023 | 165 |
| 28-05-2023 08:47 | 123 | 257003 | 250 |
| 27-05-2023 13:00 | 123 | 0 | |
| 27-05-2023 12:58 | 123 | 239700 | 405 |
| 27-05-2023 11:53 | 123 | 360027 | 0 |
| 27-05-2023 11:46 | 123 | 360020 | 0 |
| 27-05-2023 11:42 | 123 | 360021 | 0 |
| 27-05-2023 11:20 | 123 | 360026 | 198 |
| 27-05-2023 07:47 | 123 | 059002 | 150 |
| 27-05-2023 06:55 | 123 | 059001 | 85 |
| 26-05-2023 09:46 | 123 | 059001 | 165 |
| 26-05-2023 08:10 | 123 | 059002 | 300 |
| 25-05-2023 18:08 | 123 | 360026 | 0 |
| 25-05-2023 17:45 | 123 | 360021 | 127 |
| 25-05-2023 17:40 | 123 | 360020 | 0 |
| 25-05-2023 17:31 | 123 | 360027 | 71 |
| 25-05-2023 16:46 | 123 | 239700 | 405 |
| 24-05-2023 19:36 | 123 | 257003 | 250 |
| 24-05-2023 10:48 | 123 | 356023 | 165 |
| 23-05-2023 19:46 | 123 | 120001 | 120 |
| 23-05-2023 18:17 | 123 | 120002 | 140 |
| 23-05-2023 17:56 | 123 | 118002 | 145 |
| 23-05-2023 16:27 | 123 | 118001 | 145 |
| 23-05-2023 16:06 | 123 | 116001 | 75 |
| 23-05-2023 14:52 | 123 | 116003 | 265 |
| 23-05-2023 10:10 | 123 | 116003 | 525 |
| 23-05-2023 08:48 | 123 | 116001 | 155 |
| 23-05-2023 08:38 | 123 | 118001 | 295 |
| 23-05-2023 07:11 | 123 | 0 | |
| 23-05-2023 07:10 | 123 | 118002 | 295 |
| 23-05-2023 06:50 | 123 | 120002 | 285 |
| 22-05-2023 20:47 | 123 | 120001 | 245 |
| 22-05-2023 14:31 | 123 | 356023 | 165 |
| 21-05-2023 16:09 | 123 | 257002 | 260 |
| 21-05-2023 14:59 | 123 | 257001 | 310 |
| 21-05-2023 13:46 | 123 | 356008 | 520 |
| 21-05-2023 12:05 | 123 | 356007 | 550 |
| 21-05-2023 10:48 | 123 | 356006 | 550 |
| 19-05-2023 20:16 | 123 | 356006 | 550 |
| 19-05-2023 19:01 | 123 | 0 | |
| 19-05-2023 19:00 | 123 | 356007 | 550 |
| 19-05-2023 17:29 | 123 | 356008 | 520 |
| 19-05-2023 16:14 | 123 | 257001 | 310 |
| 19-05-2023 15:00 | 123 | 257002 | 260 |
| 19-05-2023 08:27 | 123 | 356023 | 165 |
| 18-05-2023 17:47 | 123 | 120001 | 245 |
| 18-05-2023 16:31 | 123 | 120002 | 285 |
| 18-05-2023 16:12 | 123 | 118002 | 295 |
| 18-05-2023 13:59 | 123 | 118001 | 145 |
| 18-05-2023 13:37 | 123 | 116001 | 155 |
| 18-05-2023 12:22 | 123 | 116003 | 265 |
| 17-05-2023 18:23 | 123 | 0 | |
| 17-05-2023 18:21 | 123 | 116003 | 525 |
| 17-05-2023 16:31 | 123 | 118001 | 295 |
| 17-05-2023 14:51 | 123 | 118002 | 295 |
| 17-05-2023 14:29 | 123 | 120002 | 285 |
| 17-05-2023 12:55 | 123 | 120001 | 245 |
| 17-05-2023 06:42 | 123 | 356023 | 165 |
| 15-05-2023 06:46 | 123 | 356023 | 165 |
| 14-05-2023 15:51 | 123 | 120001 | 245 |
| 14-05-2023 14:23 | 123 | 120002 | 285 |
| 14-05-2023 14:04 | 123 | 118002 | 295 |
| 14-05-2023 12:36 | 123 | 118001 | 145 |
| 14-05-2023 12:11 | 123 | 116001 | 75 |
| 14-05-2023 09:37 | 123 | 116003 | 265 |
| 13-05-2023 17:25 | 123 | 116003 | 525 |
| 13-05-2023 15:27 | 123 | 116001 | 155 |
| 13-05-2023 15:13 | 123 | 0 | |
| 13-05-2023 15:09 | 123 | 118001 | 295 |
| 13-05-2023 13:27 | 123 | 118002 | 295 |
| 13-05-2023 13:07 | 123 | 120002 | 285 |
| 13-05-2023 11:22 | 123 | 120001 | 245 |
| 12-05-2023 19:02 | 123 | 356023 | 165 |
| 12-05-2023 08:19 | 123 | 257002 | 260 |
| 12-05-2023 07:02 | 123 | 257001 | 310 |
| 11-05-2023 20:12 | 123 | 356008 | 520 |
| 11-05-2023 18:33 | 123 | 356007 | 550 |
| 11-05-2023 17:24 | 123 | 356006 | 550 |
| 09-05-2023 10:53 | 123 | 356006 | 550 |
| 09-05-2023 09:41 | 123 | 0 | |
| 09-05-2023 09:40 | 123 | 356007 | 550 |
| 09-05-2023 07:58 | 123 | 356008 | 520 |
| 09-05-2023 06:45 | 123 | 257001 | 310 |
| 08-05-2023 19:42 | 123 | 257002 | 260 |
| 07-05-2023 12:15 | 123 | 257003 | 250 |
| 05-05-2023 20:02 | 123 | 257003 | 250 |
TABLE 2:
| S No | Licence Plate No | Date | 356023 | 120001 | 120002 | 118002 | 118001 | 116001 | 116003 | 116003 | 116001 | 118001 | 118002 | 120002 | 120001 | 356023 | Amount |
| 1 | 123 | 12-05-2023 | 165 | 245 | 285 | 295 | 295 | 155 | 525 | 265 | 75 | 145 | 295 | 285 | 245 | 165 | 3440 |
| 2 | 123 | 16-05-2023 | 165 | 245 | 285 | 295 | 295 | 0 | 525 | 265 | 155 | 145 | 295 | 285 | 245 | 165 | 3365 |
| 3 | 123 | 22-05-2023 | 3020 | ||||||||||||||
| 4 | 123 | 28-05-2023 | 3145 |
I would suggest changing the data on Tabelle1 to be an Excel Table so that it scales with the input data. Similarly any manual input fields on output table. I note that the 'in' and 'out' times have disappeared for some reason. The 'Plaza' codes would be created as a dynamic array (unique values, sorted) and referenced by the SUMIFS formula as a dynamic range.
I have nothing to contribute on separate workbooks; I am fortunate to be able to avoid them. It may well be that the source workbook must be open if the destination sheet is to work
=IFERROR(INDEX($D$4:$D$102,MATCH(1,(D$107=$C$4:$C$102)*($A108=$E$4:$E$102)*(D$106=$F$4:$F$102),0)),0)You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
I've added helper columns E and F and additional search criteria in range D106:Q106 in the attached file.
