lookupifs?? lookup value so long as date value is greater than specified date

[ajm]

I am working with an extract from our contracts database and a list of supplier expenditure. From the list of supplier spend, i have identified our Top100 suppliers. I now need to identify to which Category each supplier belongs. if the contracts data consisted of a single entry per supplier, this would not be difficult. However, with contracts, each supplier may have multiple contracts in their own right or they may be one of several parties to a contract. The contracts data can also contain expired contracts.

mockup data below
Basic spend data

Company Name	Expenditure	Category
Apples	1000
Bondi P/L	900	Construction
**** a doodle doo	750
Dingo Dog Ltd	725
Echo echo	690	Sonar
Frogs Red.yum yum	574.5
Glub Pty Ltd	495	Chemicals
Hod P/L	415.5
I-Wash	336
J-Lo Inc	256.5

Mock up Contract data

Party	Contract Number	Contract Description	Start Date	End Date	Category
Bondi P/L	K3456	Good Stuff	29/10/2020	28/04/2021	Support Services
Glub Pty Ltd	K9876	Bad Stuff	7/09/2020	6/09/2021	Software & Licences
Hod P/L, J-Lo Inc, Brother B, Glub Pty Ltd	K5264	Stuff you	24/11/2020	23/11/2021	Software & Licences
Dingo Dog Ltd, Glub Pty Ltd,E	K8356	Your Stuff	2/10/2020	1/12/2021	ICT Services
Frogs Red.yum yum	K2739	My Stuff	23/05/2023	31/07/2023	Fleet & Fuel
Frogs Red.yum yum	K6100	Not My Stuff	1/09/2018	31/08/2023	Construction
Bondi P/L	K6022	Who's Stuff is this	7/10/2021	6/10/2024	Construction
Bondi P/L	K8008	Stuff it	1/08/2022	1/12/2024	Construction
Bondi P/L	K7355	OK Stuff	1/04/2024	20/12/2024	Construction
Glub Pty Ltd	K9087	Holiday stuffing	21/05/2024	20/05/2025	Chemicals
Frogs Red.yum yum, Dingo Dog Ltd	K4069	Get Stuffed	1/07/2017	30/06/2025
Glub Pty Ltd			14/06/2022	14/07/2025
Echo echo			1/02/2024	31/01/2027	Sonar
			7/05/2024	6/05/2027

so, the result i am after is a lookup that returns the category specified in the contracts data so long as the end date has not already passed. Take Glub Pty Ltd as an example. The first three contracts involving Glub have expired so I don't want the Category associated with these. The 4th time it is listed in the contract data (K9087), the expiry date is in the future so i want that Category (Chemicals). Another example would be Bondi P/L. Contract K6022 has expired, but the next one K8008 has not.

 

[Peter_SSs]

Is this what you mean?
If there is more than one contract meeting the criteria, this formula returns the first (but it could return all if required & such a circumstance is possible)
I have assumed dates in col J will be ascending per your sample.

24 10 09.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Company Name	Expenditure	Category			Party	Contract Number	Contract Description	Start Date	End Date	Category
2	Apples	1000				Bondi P/L	K3456	Good Stuff	29/10/2020	28/04/2021	Support Services
3	Bondi P/L	900	Construction			Glub Pty Ltd	K9876	Bad Stuff	7/09/2020	6/09/2021	Software & Licences
4	**** a doodle doo	750				Hod P/L, J-Lo Inc, Brother B, Glub Pty Ltd	K5264	Stuff you	24/11/2020	23/11/2021	Software & Licences
5	Dingo Dog Ltd	725				Dingo Dog Ltd, Glub Pty Ltd,E	K8356	Your Stuff	2/10/2020	1/12/2021	ICT Services
6	Echo echo	690	Sonar			Frogs Red.yum yum	K2739	My Stuff	23/05/2023	31/07/2023	Fleet & Fuel
7	Frogs Red.yum yum	574.5				Frogs Red.yum yum	K6100	Not My Stuff	1/09/2018	31/08/2023	Construction
8	Glub Pty Ltd	495	Chemicals			Bondi P/L	K6022	Who's Stuff is this	7/10/2021	6/10/2024	Construction
9	Hod P/L	415.5				Bondi P/L	K8008	Stuff it	1/08/2022	1/12/2024	Construction
10	I-Wash	336				Bondi P/L	K7355	OK Stuff	1/04/2024	20/12/2024	Construction
11	J-Lo Inc	256.5				Glub Pty Ltd	K9087	Holiday stuffing	21/05/2024	20/05/2025	Chemicals
12						Frogs Red.yum yum, Dingo Dog Ltd	K4069	Get Stuffed	1/07/2017	30/06/2025
13						Glub Pty Ltd			14/06/2022	14/07/2025
14						Echo echo			1/02/2024	31/01/2027	Sonar
15									7/05/2024	6/05/2027
Category

Cell Formulas
Range	Formula
C2:C11	C2	=INDEX(FILTER(K$2:K$15&"",ISNUMBER(SEARCH(A2,F$2:F$15))*(J$2:J$15>=TODAY()),""),1)

 

[ajm]

Peter, what on earth did you create? are you a witch? its perfect. can you give me a little detail on how, why, and what?

 

[Peter_SSs]

can you give me a little detail on how, why, and what?

It filters col K for the rows where the relevant company name appears in col F** and the col J date is today or later then takes the first col K value that is left after that filter.

** This name part could be a little problematic if you do really have short names like "Hod P/L". If another company was called "Flashod P/L" then an incorrect result could occur because the short name can be found within the longer name. If that is possible a more complicated formula would be required.

 

[ajm]

thanks Pete. the names are company names as input to our ERP. (fingers crossed) there is a very small chance one name could appear within another. thinking family name businesses might be an issue (eg "J. Smith Pty Ltd" and P, R, S and J. Smith Pty Ltd) but i will do a manual check on these. thankfully, there aren't that many. cheers again.

 

[Peter_SSs]

Here is a smaller/changed sample that shows the incorrect result in C3 with the previous formula in column C and the correct result in D3 with a replacement formula in column D that you could use in case.

ajm.xlsm
	A		C	D	E	F				J	K
1	Company Name		Category	Category		Party				End Date	Category
2	Glub Pty Ltd		Software & Licences	Software & Licences		Bondi P/L				29/10/2025	Support Services
3	Hod P/L		ICT Services	Software & Licences		Bondi P/L, Flashod P/L				7/09/2025	ICT Services
4	Flashod P/L		ICT Services	ICT Services		Hod P/L, J-Lo Inc, Brother B, Glub Pty Ltd				24/11/2025	Software & Licences
5	Apples					Dingo Dog Ltd, Glub Pty Ltd,E				2/10/2025	ICT Services
6	J-Lo Inc		Software & Licences	Software & Licences		Frogs Red.yum yum				23/05/2028	Fleet & Fuel
7						Frogs Red.yum yum				1/09/2023	Construction
8						Glub Pty Ltd				21/05/2029	Chemicals
9						Frogs Red.yum yum, Dingo Dog Ltd				1/07/2022
10						Glub Pty Ltd				14/06/2027
Category (2)

Cell Formulas
Range	Formula
C2:C6	C2	=INDEX(FILTER(K$2:K$12&"",ISNUMBER(SEARCH(A2,F$2:F$12))*(J$2:J$12>=TODAY()),""),1)
D2:D6	D2	=INDEX(FILTER(K$2:K$12&"",ISNUMBER(SEARCH(","&A2&",",","&SUBSTITUTE(F$2:F$12,", ",",")&","))*(J$2:J$12>=TODAY()),""),1)