Forum Discussion
Cantona
Feb 02, 2023Copper Contributor
Compare range to a row and return value from range
Seeking any help or guidance. Projects will pay amounts listed in table A, on the dates listed in table B. I'm attempting to combine these two tables in format shown in table C. So if the Payment Date matches date in first row in table C, I should be able to pull the payment that was made on that date and populate table C. Apologies if this is amateurish or I'm missing something basic. Thanks.
Table A
| Payment 1 | Payment 2 | Payment 3 | Payment 4 | |
| Project A | $0.49 | $0.79 | $0.79 | $0.15 |
| Project B | $0.19 | $0.84 | $0.01 | $0.32 |
| Project C | $0.18 | $0.12 | $0.30 | $0.12 |
| Project D | $0.22 | $0.50 | $0.99 | $0.98 |
Table B:
| Payment 1 Date | Payment 2 Date | Payment 3 Date | Payment 4 Date | |
| Project A | Jan-2018 | Nov-2019 | Oct-2020 | Apr-2021 |
| Project B | Jan-2019 | Nov-2019 | Oct-2020 | Apr-2021 |
| Project C | Jan-2018 | Nov-2020 | Oct-2020 | Apr-2020 |
| Project D | Jan-2018 | Nov-2019 | Oct-2020 | Apr-2021 |
Table C:
| Jan-2018 | Feb-2018 | Mar-2018 | Apr-2018 | May 2018 | Jun-2018 | Jul-2018 | Aug-2018 | Sep-2018 | Oct-2018 | Nov-2018 | Dec-2018 | |
| Project A | ||||||||||||
| Project B | ||||||||||||
| Project C | ||||||||||||
| Project D |
Try this:
Sample file is on the attached.