Hi,
I have a table that has below format and need to update the amount from different table that has a different format, I tried using Xlookup and Index/Match but for some reason it didn't work. Could anyone help me?
What I need to accomplish:
- The template I have has 3 columns (Employee ID, Effective Date & Amount) and all of these columns are blank. Is there a way that I could update the Employee ID and Effective date base on Table 2, rather than manually copying the 3 dates from table 2 (paste them into transpose to update the Effective Date column) and the 4 Employee ID (repeat 3 times)?
- Update the amount column from Table 2 in Table 1 (Amount column) based on the Effective Date and Employee ID
I have a table that has below format and need to update the amount from different table that has a different format, I tried using Xlookup and Index/Match but for some reason it didn't work. Could anyone help me?
What I need to accomplish:
- The template I have has 3 columns (Employee ID, Effective Date & Amount) and all of these columns are blank. Is there a way that I could update the Employee ID and Effective date base on Table 2, rather than manually copying the 3 dates from table 2 (paste them into transpose to update the Effective Date column) and the 4 Employee ID (repeat 3 times)?
- Update the amount column from Table 2 in Table 1 (Amount column) based on the Effective Date and Employee ID
| XLOOKUP with multiple criteria.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Table 1 | Table 2 (different workbook) | ||||||||||||||
| 2 | Required Result | Employee ID | 2022-03-31 | 2022-04-30 | 2022-05-31 | |||||||||||
| 3 | Employee ID | Effective Date* | Amount | AA | $3,000.00 | $2,250.00 | $30,271.50 | |||||||||
| 4 | AA | 2022-03-31 | $ 3,000.00 | BB | $ - | $5,000.00 | $16,321.50 | |||||||||
| 5 | BB | 2022-03-31 | $ - | CC | $1,500.00 | $ - | $15,856.50 | |||||||||
| 6 | CC | 2022-03-31 | $ 1,500.00 | DD | $3,250.00 | $2,050.00 | $ - | |||||||||
| 7 | DD | 2022-03-31 | $ 3,250.00 | |||||||||||||
| 8 | AA | 2022-04-30 | $ 2,250.00 | |||||||||||||
| 9 | BB | 2022-04-30 | $ 5,000.00 | |||||||||||||
| 10 | CC | 2022-04-30 | $ - | |||||||||||||
| 11 | DD | 2022-04-30 | $ 2,050.00 | |||||||||||||
| 12 | AA | 2022-05-31 | $ 30,271.50 | |||||||||||||
| 13 | BB | 2022-05-31 | $ 16,321.50 | |||||||||||||
| 14 | CC | 2022-05-31 | $ 15,856.50 | |||||||||||||
| 15 | DD | 2022-05-31 | $ - | |||||||||||||
| 16 | ||||||||||||||||
| 17 | ||||||||||||||||
| 18 | ||||||||||||||||
Sheet3 | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J2 | J2 | =A3 |
