loop though dates column and if matched get value from the next column

[nikolay100]

Hi everyone,

So I have the following table:

Date1	Currency		Date2
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.

 

[nikolay100]

Apologies I used the wrong examples. Here is the correct data:

Date1	Currency		Date2
2023-02-01	1.79533		2023-02-22
2023-02-02	1.77997		2023-02-04
2023-02-03	1.78827		2023-02-25
2023-02-06	1.81499		2023-02-22
2023-02-07	1.82788		2023-02-22
2023-02-08	1.82192		2023-02-18
2023-02-09	1.81583		2023-02-22
2023-02-10	1.82959		2023-02-22
2023-02-13	1.83027		2023-02-12
2023-02-14	1.81785		2023-02-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 closest date in the past from this range.

For example:
In case of the first row cell in Date2 - 2023-02-22, since there is a match it should select 1.8375.
In case of the second row cell in Date2 - 2023-02-04, there is no match and it should select the value from 2023-02-03, which is 1.78827

 

[Dave Patton]

Consider the example below.
N.B. It is much better to post with the forum's tool named XL2BB. I converted the text to dates.

T202303a.xlsm
	A	B	C	D	E
1	Date1	Currency		Date2
2	1-Feb-23	1.79533		22-Feb-23	1.8375
3	2-Feb-23	1.77997		4-Feb-23	1.78827
4	3-Feb-23	1.78827
5	6-Feb-23	1.81499
6	7-Feb-23	1.82788
7	8-Feb-23	1.82192
8	9-Feb-23	1.81583
9	10-Feb-23	1.82959
10	13-Feb-23	1.83027
11	14-Feb-23	1.81785
12	15-Feb-23	1.82788
13	16-Feb-23	1.82788
14	17-Feb-23	1.84078
15	20-Feb-23	1.83233
16	21-Feb-23	1.83405
17	22-Feb-23	1.8375
18	23-Feb-23	1.84234
19	24-Feb-23	1.85036
20	27-Feb-23	1.85316
21	28-Feb-23	1.84182
5a

Cell Formulas
Range	Formula
E2:E3	E2	=LOOKUP(D2,$A$2:$B$21)