Forum Discussion
How do I pull data from one sheet into another based on multiple matching criteria
I have a book1(monthly vehicle toll tag statement) table1. i need to pull amount for particular plaza on particular date for entire month (table2). Any help would be much appreciated. Transa...
SUMIFS provide an alternative formulation that will allow the results to be generated with a single dynamic array formula.
= SUMIFS(amount,
licencePlate, plate,
transactionDate, ">="&dateIn,
transactionDate, "<="&dateOut,
PlazaCode, code
)
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.
note: table 1 and table 2 are in different workbook
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 |