We have two tables in Google Sheets.
First:
| Date | Amount | Currency | Worth |
|---|---|---|---|
| 01.01.2021 | 100 | USD | 373 |
| 02.01.2021 | 100 | EUR | 451 |
| 03.01.2021 | 100 | PLN | 100 |
| 04.01.2021 | 100 | USD | 373 |
| 05.01.2021 | 100 | USD | 372 |
Second:
| Date | PLN | EUR | USD |
|---|---|---|---|
| 01.01.2021 | 1 | 4,50 | 3,73 |
| 02.01.2021 | 1 | 4,51 | 3,75 |
| 03.01.2021 | 1 | 4,50 | 3,74 |
| 04.01.2021 | 1 | 4,48 | 3,73 |
| 05.01.2021 | 1 | 4,49 | 3,72 |
I tried find array formula for first table, column Worth. Formula should take proper value from second table (based on two columns from table one - Date and Currency) and multiply that values by worth in column Amount. I really want to use array formula. Is it possible?
CodePudding user response：
Use VLOOKUP to find the correct date row and MATCH to find which column the value is in:
=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A,I2:L,MATCH(C2:C,I1:L1,0))*B2:B))
CodePudding user response：
Option 01: Getting the result with one cell one formula.
Paste this in B3 "Amount" column in the first table, take a look at 
Explanation ...
1 - 