I am trying to write an autofilling arrayfomula in Google Sheets. The formula should look up a price from a lookup table into the main table using 4 criteria.I feel like this is something I should be able to do easily, but the dates are throwing me off.
I can do this with index match, filters, queries, and some combinations, but I can’t seem to figure out how to do it in a manner that will autofill, as rows will be added to the table regularly.
Information:
- This is essentially a 4 criteria lookup (fruit, date greater than start date, date less than end date, Qty)
- There are no patterns with the pricing
- The prices are located in the lookup table under columns 1, 2, 3, 4, 5, 6
- The date from the main table should be between the start date and end date column in the lookup table
Main Table – Fill in Price column from the Lookup Table
| Fruit | Date | Qty | Price |
| ——– | ——– | ——– | ——– |
| Apple | 1/10/2021 | 5 | |
| Apple | 4/20/2021 | 3 | |
| Orange | 7/19/2021 | 5 | |
| Strawberry | 10/19/2021 | 5 | |
| Grape | 1/19/2022 | 3 | |
| Banana | 4/18/2022 | 1 | |
| Orange | 7/11/2022 | 1 | |
| Strawberry | 10/10/2022 | 6 | |
| Grape | 1/13/2023 | 1 | |
| Banana | 3/3/2023 | 2 | |
Lookup Table
| Fruit | Start Date | End Date | 1 | 2 | 3 | 4 | 5 | 6 |
| ——– | ——– | ——– | ——– | ——– | ——– | ——– | ——– | ——– |
| Apple | 1/1/2019 | 1/31/2022 | 10 | 6 | 16 | 16 | 9 | 4 |
| Orange | 1/1/2019 | 1/31/2022 | 4 | 11 | 1 | 6 | 11 | 17 |
| Strawberry | 1/1/2019 | 1/31/2022 | 16 | 14 | 17 | 14 | 1 | 6 |
| Grape | 1/1/2019 | 1/31/2022 | 20 | 11 | 10 | 15 | 20 | 3 |
| Banana | 1/1/2019 | 1/31/2022 | 7 | 1 | 20 | 8 | 2 | 9 |
| Apple | 2/1/2022 | 12/1/9099 | 12 | 17 | 2 | 13 | 9 | 14 |
| Orange | 2/1/2022 | 12/1/9099 | 16 | 5 | 19 | 16 | 19 | 9 |
| Strawberry | 2/1/2022 | 12/1/9099 | 19 | 5 | 2 | 2 | 19 | 11 |
| Grape | 2/1/2022 | 12/1/9099 | 8 | 3 | 19 | 16 | 9 | 1 |
| Banana | 2/1/2022 | 12/1/9099 | 1 | 6 | 11 | 15 | 12 | 15 |
Desired Result
| Fruit | Date | Qty | Price |
| ——– | ——– | ——– | ——– |
| Apple | 1/10/2021 | 5 | 9 |
| Apple | 4/20/2021 | 3 | 16 |
| Orange | 7/19/2021 | 5 | 11 |
| Strawberry | 10/19/2021 | 5 | 1 |
| Grape | 1/19/2022 | 3 | 10 |
| Banana | 4/18/2022 | 1 | 1 |
| Orange | 7/11/2022 | 1 | 16 |
| Strawberry | 10/10/2022 | 6 | 11 |
| Grape | 1/13/2023 | 1 | 8 |
| Banana | 3/3/2023 | 2 | 6 |
Have tried using vlookup+match, filter, byrow+lambda (new to this), but can’t seem to get anything to autofill.
Prefer a formula to apps script at this stage.
>Solution :
You may try:
=map(A3:A,B3:B,C3:C,lambda(a,b,c,if(a="",,+filter(filter(Lookup!D2:I,Lookup!D1:I1=c),Lookup!A2:A=a,Lookup!B2:B<b,Lookup!C2:C>b))))
