kumara_faith
Well-known Member
- Joined
- Aug 19, 2006
- Messages
- 923
- Office Version
- 365
Hi,
I have the following sample data located in Sheet1:
Then, I have the following sample data in Sheet2:
In Sheet3, I am trying to build a lookup to pull the data from Sheet1 and Sheet2 if the policy number matches:
I managed to look the data from Sheet1 but how do I modify the FILTER formula to look at both Sheet1 and Sheet2 to check if the policy number matches and populate the matching data accordingly ? Appreciate any help.
I have the following sample data located in Sheet1:
Book1 | |||||||
---|---|---|---|---|---|---|---|
B | C | D | E | F | |||
3 | Policy No | Name | Payment Type | Last Payment Date | Payment Amount | ||
4 | 123456 | Jay | 6 | 1/4/2022 | 200 | ||
Sheet1 |
Then, I have the following sample data in Sheet2:
Book1 | |||||||
---|---|---|---|---|---|---|---|
H | I | J | K | L | |||
3 | Policy No | Name | Payment Type | Last Payment Date | Payment Amount | ||
4 | 123457 | May | 6 | 2/5/2022 | 315 | ||
Sheet1 |
In Sheet3, I am trying to build a lookup to pull the data from Sheet1 and Sheet2 if the policy number matches:
Book1 | |||||||
---|---|---|---|---|---|---|---|
O | P | Q | R | S | |||
3 | Policy No | 123456 | |||||
4 | |||||||
5 | Policy No | Name | Payment Type | Last Payment Date | Payment Amount | ||
6 | 123456 | Jay | 6 | 44652 | 200 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
O6:S6 | O6 | =FILTER(B4:F4,B4=P3) |
Dynamic array formulas. |
I managed to look the data from Sheet1 but how do I modify the FILTER formula to look at both Sheet1 and Sheet2 to check if the policy number matches and populate the matching data accordingly ? Appreciate any help.