SEARCH TEXT BETWEEN DATES

[IGORRRRR]

Good morning,

I need the help to solve a problem.

I need to search one text in a table with a range of date:

Supplier Date Value
A 19/10/2022 Madrid
A 05/10/2022 Barcelona
B 17/10/2022 Zaragoza
B 09/10/2022 Sevilla

Table when searching:

Supplier Initial Date Final Date Value
A 15/10/2022 20/10/2022 Madrid
A 01/10/2022 14/10/2022 Barcelona
B 10/10/2022 29/10/2022 Zaragoza
B 01/10/2022 09/10/2022 Sevilla

Thanks in advance

 

[alansidman]

Your explanation is very vague. Please explain your needs in addition to your presentation.

 

[IGORRRRR]

Sorry for the explanation;

I have a table 1 with the following information:

Table 1
Supplier	Initial Date	Final Date	Value
A	15/10/2022	20/10/2022	Madrid
A	01/10/2022	14/10/2022	Barcelona
B	10/10/2022	29/10/202	Zaragoza
B	01/10/2022	09/10/202	Sevilla

What I expect is when I introduce the supplier and the date in the table 2, automatically and with a formule search the value of the table 1 depending of the intervale of dates and supplier:

Table 2
Supplier	Date	Value
A	19/10/2022	Madrid
A	05/10/2022	Barcelona
B	17/10/2022	Zaragoza
B	09/10/2022	Sevilla

I insert one image to undertand better the problem.

I found one solution but only works with numeric values, not when I search one text.

Thanks in advance

 

[IGORRRRR]

Could somebody help me to this question?

 

[IGORRRRR]

Could somebody help me to this question?

 

[Peter_SSs]

Assuming that only one row in the first table will satisfy the criteria, try this.

22 10 27.xlsm
	A	B	C	D	E	F	G	H
1	Supplier	Initial Date	Final Date	Value		Supplier	Date	Value
2	A	15/10/2022	20/10/2022	Madrid		A	19/10/2022	Madrid
3	A	1/10/2022	14/10/2022	Barcelona		A	5/10/2022	Barcelona
4	B	10/10/2022	29/10/2022	Zaragoza		B	17/10/2022	Zaragoza
5	B	1/10/2022	9/10/2022	Sevilla		B	9/10/2022	Sevilla
Lookup

Cell Formulas
Range	Formula
H2:H5	H2	=INDEX(D:D,SUMPRODUCT(--(A$2:A$5=F2),--(B$2:B$5<=G2),--(C$2:C$5>=G2),ROW(D$2:D$5)))

 

[IGORRRRR]

Thank-you for the answer, it works perfectly.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.