nikolay100
New Member
- Joined
- Oct 14, 2022
- Messages
- 6
- Office Version
- 2021
- Platform
- Windows
Hi everyone,
So I have the following table:
The idea is to compare each cell from Date2 column with each cell from Date1 column and if there is a match to get the value from the currency column. And then there is a the tricky part, if there is no match it should pick the the closest date in the past from this range.
For example:
In case of the first row cell in Date2 - 2023-03-22, since there is a match it should select 1.8375.
In case of the second row cell in Date2 - 2023-03-04, there is no match and it should select the value from 2023-02-03, which is 1.78827
Is this achievable with a regular formula? If not then, what would be the best solution for this?
Thanks in advance.
So I have the following table:
|
|
| ||||
---|---|---|---|---|---|---|
2023-02-01 | 1.79533 | 2023-03-22 | ||||
2023-02-02 | 1.77997 | 2023-03-04 | ||||
2023-02-03 | 1.78827 | 2023-03-25 | ||||
2023-02-06 | 1.81499 | 2023-03-22 | ||||
2023-02-07 | 1.82788 | 2023-03-22 | ||||
2023-02-08 | 1.82192 | 2023-03-18 | ||||
2023-02-09 | 1.81583 | 2023-03-22 | ||||
2023-02-10 | 1.82959 | 2023-03-22 | ||||
2023-02-13 | 1.83027 | 2023-03-12 | ||||
2023-02-14 | 1.81785 | 2023-03-22 | ||||
2023-02-15 | 1.82788 | |||||
2023-02-16 | 1.82788 | |||||
2023-02-17 | 1.84078 | |||||
2023-02-20 | 1.83233 | |||||
2023-02-21 | 1.83405 | |||||
2023-02-22 | 1.8375 | |||||
2023-02-23 | 1.84234 | |||||
2023-02-24 | 1.85036 | |||||
2023-02-27 | 1.85316 | |||||
2023-02-28 | 1.84182 |
The idea is to compare each cell from Date2 column with each cell from Date1 column and if there is a match to get the value from the currency column. And then there is a the tricky part, if there is no match it should pick the the closest date in the past from this range.
For example:
In case of the first row cell in Date2 - 2023-03-22, since there is a match it should select 1.8375.
In case of the second row cell in Date2 - 2023-03-04, there is no match and it should select the value from 2023-02-03, which is 1.78827
Is this achievable with a regular formula? If not then, what would be the best solution for this?
Thanks in advance.