Finding Price from Matching Date and Yarn ID.

[Vishnu75]

Hi All,


Greetings to all.

I have a serious excel problem where I'm trying to find the <Price> in Table1 matching to 2 criteria in Table2.

Now, the issue is that <Created Date> in Table1 does not automatically exactly match that in Table2.


Which means:

1. that will need to do a close match of Table1<Created Date> with Table2<Created Date> with respective Yarn ID. (PS: One <Yarn Id> have multiple <Created Date> & multiple <Price>.
2. With above done, we will have <Matching Exact Created Date> & <Yarn ID>, we can then get the Exact <Price> from Table2.

Have paste the two tables below, please help.

Thanks

Table1
Created Date	Yarn ID	Find Price?
21/07/2022	2546
29/06/2022	1990
29/07/2022	1084
29/07/2022	1233
05/09/2022	1543
05/09/2022	1543
07/07/2022	1202
20/07/2022	1990

Table2
Created Date	Yarn ID	Price
16/11/2022	1084	5.60
16/12/2022	1084	5.60
16/02/2023	1084	6.00
10/11/2021	1202	5.05
10/11/2021	1202	5.25
10/11/2021	1202	5.05
15/11/2021	1202	5.05
14/01/2022	1202	5.25
14/01/2022	1202	5.25
14/01/2022	1202	5.25
19/01/2022	1202	5.35
19/01/2022	1202	5.35
27/01/2022	1202	5.20
27/01/2022	1202	5.20
03/02/2022	1202	5.30
04/02/2022	1202	5.30
04/02/2022	1202	5.40
04/02/2022	1202	5.40
05/05/2022	1202	5.05
05/05/2022	1202	5.05
05/05/2022	1202	5.05
11/07/2022	1202	5.10
11/07/2022	1202	5.10
11/07/2022	1202	5.10
11/07/2022	1202	5.10
05/08/2022	1202	4.55
24/08/2022	1202	6.10
29/08/2022	1202	5.25
29/08/2022	1202	5.25
29/08/2022	1202	5.25
06/09/2022	1202	4.79
06/09/2022	1202	4.79
08/09/2022	1202	4.79
08/09/2022	1202	4.79
13/09/2022	1202	4.85
13/09/2022	1202	4.85
29/09/2022	1202	3.80
13/10/2022	1202	3.80
13/10/2022	1202	3.80
14/10/2022	1202	3.85
14/10/2022	1202	3.85
14/10/2022	1202	3.85
14/10/2022	1202	3.85
14/10/2022	1202	3.85
10/11/2022	1202	3.60
10/11/2022	1202	3.60
11/11/2022	1202	3.60
16/11/2022	1202	3.88
16/11/2022	1202	3.88
16/11/2022	1202	3.88
16/11/2022	1202	3.88
09/12/2022	1202	3.47
25/01/2023	1202	3.50
16/02/2023	1202	3.50
23/02/2023	1202	3.65
23/02/2023	1202	3.65
23/02/2023	1202	3.65
01/03/2023	1202	3.65
02/03/2023	1202	3.50
03/03/2023	1202	3.65
10/03/2023	1202	3.45
10/03/2023	1202	3.45
24/03/2023	1202	3.48
24/03/2023	1202	3.48
24/03/2023	1202	3.48
06/04/2023	1202	3.55
10/04/2023	1202	3.55
13/04/2023	1202	3.55
26/04/2023	1202	3.35
26/04/2023	1202	3.35
28/04/2023	1202	3.50
28/04/2023	1202	3.50
10/11/2021	1233	7.60
28/02/2022	1233	7.70
07/07/2022	1233	7.50
19/08/2022	1233	6.35
16/11/2022	1233	5.75
16/12/2022	1233	5.75
20/12/2021	1990	7.50
08/06/2022	1990	6.95
20/12/2021	2546	5.55
26/01/2022	2546	6.00
08/06/2022	2546	6.60
26/08/2022	2546	6.60
17/10/2022	2546	6.60
28/10/2022	2546	6.60
31/10/2022	2546	6.60
16/02/2023	2546	5.35

 

[AlanY]

try this

Book1
	A	B	C
1	Table1
2	Created Date	Yarn ID	Find Price?
3	21/07/2022	2546	6.6
4	29/06/2022	1990	6.95
5	29/07/2022	1084	5.6
6	29/07/2022	1233	6.35
7	05/09/2022	1543	6.35
8	05/09/2022	1543	6.35
9	07/07/2022	1202	5.1
10	20/07/2022	1990	6.95
11
12	Table2
13	Created Date	Yarn ID	Price
14	16/11/2022	1084	5.6
15	16/12/2022	1084	5.6
16	16/02/2023	1084	6
17	10/11/2021	1202	5.05
18	10/11/2021	1202	5.25
19	10/11/2021	1202	5.05
20	15/11/2021	1202	5.05
21	14/01/2022	1202	5.25
22	14/01/2022	1202	5.25
23	14/01/2022	1202	5.25
24	19/01/2022	1202	5.35
25	19/01/2022	1202	5.35
26	27/01/2022	1202	5.2
27	27/01/2022	1202	5.2
28	03/02/2022	1202	5.3
29	04/02/2022	1202	5.3
30	04/02/2022	1202	5.4
31	04/02/2022	1202	5.4
32	05/05/2022	1202	5.05
33	05/05/2022	1202	5.05
34	05/05/2022	1202	5.05
35	11/07/2022	1202	5.1
36	11/07/2022	1202	5.1
37	11/07/2022	1202	5.1
38	11/07/2022	1202	5.1
39	05/08/2022	1202	4.55
40	24/08/2022	1202	6.1
41	29/08/2022	1202	5.25
42	29/08/2022	1202	5.25
43	29/08/2022	1202	5.25
44	06/09/2022	1202	4.79
45	06/09/2022	1202	4.79
46	08/09/2022	1202	4.79
47	08/09/2022	1202	4.79
48	13/09/2022	1202	4.85
49	13/09/2022	1202	4.85
50	29/09/2022	1202	3.8
51	13/10/2022	1202	3.8
52	13/10/2022	1202	3.8
53	14/10/2022	1202	3.85
54	14/10/2022	1202	3.85
55	14/10/2022	1202	3.85
56	14/10/2022	1202	3.85
57	14/10/2022	1202	3.85
58	10/11/2022	1202	3.6
59	10/11/2022	1202	3.6
60	11/11/2022	1202	3.6
61	16/11/2022	1202	3.88
62	16/11/2022	1202	3.88
63	16/11/2022	1202	3.88
64	16/11/2022	1202	3.88
65	09/12/2022	1202	3.47
66	25/01/2023	1202	3.5
67	16/02/2023	1202	3.5
68	23/02/2023	1202	3.65
69	23/02/2023	1202	3.65
70	23/02/2023	1202	3.65
71	01/03/2023	1202	3.65
72	02/03/2023	1202	3.5
73	03/03/2023	1202	3.65
74	10/03/2023	1202	3.45
75	10/03/2023	1202	3.45
76	24/03/2023	1202	3.48
77	24/03/2023	1202	3.48
78	24/03/2023	1202	3.48
79	06/04/2023	1202	3.55
80	10/04/2023	1202	3.55
81	13/04/2023	1202	3.55
82	26/04/2023	1202	3.35
83	26/04/2023	1202	3.35
84	28/04/2023	1202	3.5
85	28/04/2023	1202	3.5
86	10/11/2021	1233	7.6
87	28/02/2022	1233	7.7
88	07/07/2022	1233	7.5
89	19/08/2022	1233	6.35
90	16/11/2022	1233	5.75
91	16/12/2022	1233	5.75
92	20/12/2021	1990	7.5
93	08/06/2022	1990	6.95
94	20/12/2021	2546	5.55
95	26/01/2022	2546	6
96	08/06/2022	2546	6.6
97	26/08/2022	2546	6.6
98	17/10/2022	2546	6.6
99	28/10/2022	2546	6.6
100	31/10/2022	2546	6.6
101	16/02/2023	2546	5.35
Sheet1

Cell Formulas
Range	Formula
C3:C10	C3	=INDEX($C$14:$C$101, MATCH(MIN(SQRT(($A$14:$A$101-A3)^2 + ($B$14:$B$101-B3)^2)), SQRT(($A$14:$A$101-A3)^2 + ($B$14:$B$101-B3)^2), 0))

 

[Vishnu75]

Hi Alan,
It Works!
Thanks very much.
Can you please explain this to me..

 

[Vishnu75]

Hi Alan,
It Works!
Thanks very much.
Can you please explain this to me..

I checked, it does not give the closest date.
Actually, it returns one of the date matching to the Yarn ID (
You are the closest reply till now..

 

[Fluff]

What are your expected results & why?

 

[AlanY]

how about this? will pick up the non-existence ID in table 2

Book1
	A	B	C
1	Table1
2	Created Date	Yarn ID	Find Price?
3	21/07/2022	2546	6.6
4	29/06/2022	1990	6.95
5	29/07/2022	1084	5.6
6	29/07/2022	1233	6.35
7	05/09/2022	1543	#N/A
8	05/09/2022	1543	#N/A
9	07/07/2022	1202	5.1
10	20/07/2022	1990	6.95
11
12	Table2
13	Created Date	Yarn ID	Price
14	16/11/2022	1084	5.6
15	16/12/2022	1084	5.6
16	16/02/2023	1084	6
17	10/11/2021	1202	5.05
18	10/11/2021	1202	5.25
19	10/11/2021	1202	5.05
20	15/11/2021	1202	5.05
21	14/01/2022	1202	5.25
22	14/01/2022	1202	5.25
23	14/01/2022	1202	5.25
24	19/01/2022	1202	5.35
25	19/01/2022	1202	5.35
26	27/01/2022	1202	5.2
27	27/01/2022	1202	5.2
28	03/02/2022	1202	5.3
29	04/02/2022	1202	5.3
30	04/02/2022	1202	5.4
31	04/02/2022	1202	5.4
32	05/05/2022	1202	5.05
33	05/05/2022	1202	5.05
34	05/05/2022	1202	5.05
35	11/07/2022	1202	5.1
36	11/07/2022	1202	5.1
37	11/07/2022	1202	5.1
38	11/07/2022	1202	5.1
39	05/08/2022	1202	4.55
40	24/08/2022	1202	6.1
41	29/08/2022	1202	5.25
42	29/08/2022	1202	5.25
43	29/08/2022	1202	5.25
44	06/09/2022	1202	4.79
45	06/09/2022	1202	4.79
46	08/09/2022	1202	4.79
47	08/09/2022	1202	4.79
48	13/09/2022	1202	4.85
49	13/09/2022	1202	4.85
50	29/09/2022	1202	3.8
51	13/10/2022	1202	3.8
52	13/10/2022	1202	3.8
53	14/10/2022	1202	3.85
54	14/10/2022	1202	3.85
55	14/10/2022	1202	3.85
56	14/10/2022	1202	3.85
57	14/10/2022	1202	3.85
58	10/11/2022	1202	3.6
59	10/11/2022	1202	3.6
60	11/11/2022	1202	3.6
61	16/11/2022	1202	3.88
62	16/11/2022	1202	3.88
63	16/11/2022	1202	3.88
64	16/11/2022	1202	3.88
65	09/12/2022	1202	3.47
66	25/01/2023	1202	3.5
67	16/02/2023	1202	3.5
68	23/02/2023	1202	3.65
69	23/02/2023	1202	3.65
70	23/02/2023	1202	3.65
71	01/03/2023	1202	3.65
72	02/03/2023	1202	3.5
73	03/03/2023	1202	3.65
74	10/03/2023	1202	3.45
75	10/03/2023	1202	3.45
76	24/03/2023	1202	3.48
77	24/03/2023	1202	3.48
78	24/03/2023	1202	3.48
79	06/04/2023	1202	3.55
80	10/04/2023	1202	3.55
81	13/04/2023	1202	3.55
82	26/04/2023	1202	3.35
83	26/04/2023	1202	3.35
84	28/04/2023	1202	3.5
85	28/04/2023	1202	3.5
86	10/11/2021	1233	7.6
87	28/02/2022	1233	7.7
88	07/07/2022	1233	7.5
89	19/08/2022	1233	6.35
90	16/11/2022	1233	5.75
91	16/12/2022	1233	5.75
92	20/12/2021	1990	7.5
93	08/06/2022	1990	6.95
94	20/12/2021	2546	5.55
95	26/01/2022	2546	6
96	08/06/2022	2546	6.6
97	26/08/2022	2546	6.6
98	17/10/2022	2546	6.6
99	28/10/2022	2546	6.6
100	31/10/2022	2546	6.6
101	16/02/2023	2546	5.35
Sheet1

Cell Formulas
Range	Formula
C3:C10	C3	=INDEX($C$14:$C$101, MATCH(MIN(ABS(A3-(B3=$B$14:$B$101)*$A$14:$A$101)), ABS((B3=$B$14:$B$101)*A3-$A$14:$A$101), 0))

 

[Vishnu75]

HI Alan,
In the module where both Table1 & Table2 are on same Sheet, it works perfectly.
But, actually, Table1 and Table2 are on separate sheets and I can't get the result, Why?

 

[Vishnu75]

What are your expected results & why?

Hi Fluff,

1. Expected Results: I want to extract the Price on Table2 by using the Created Date & YarnID on Table1 matching to same on Table2. NOw that only the CreatedDate on Table1 does not exactly match same on Table2 and hence I will need to get the closest date possible.
2. Why? - Actually, I have 2 Tables (Like explain above in different Sheets) and have <Price> information to extract for 3 YarnID column and final will be the Total Price.

Hope it clear what I'm trying to explain.

 

[Fluff]

Hope it clear what I'm trying to explain.

No that is why I asked you what your expected results.

 

[Vishnu75]

Hi Fluff,

1. Expected Results: I want to extract the Price on Table2 by using the Created Date & YarnID on Table1 matching to same on Table2. NOw that only the CreatedDate on Table1 does not exactly match same on Table2 and hence I will need to get the closest date possible.
2. Why? - Actually, I have 2 Tables (Like explain above in different Sheets) and have <Price> information to extract for 3 YarnID column and final will be the Total Price.

Hope it clear what I'm trying to explain.

I Can send the whole table if needed.