Max value lookup, using comma separated lookup cell (Formula, not VB please)

[Cwillson]

Hello Peeps!

I have a table called "customers" arranged as below;

CustID	Date
1	25/01/21
3	26/07/20
4	12/01/21
5	16/07/20
8	1/10/20

I have another separate table called "organisations" as below;

OrgID	OrgCustID	MostRecentDate
509	1
699	3, 4, 5
700	8

I'm looking for a formula which will populate the MostRecentDate column in this table with the MAX date from all associated rows in the "customers" table, using the comma separated values in OrgCustID. Ugh!

Solution needs to be a formula rather than VBS I'm afraid.

Many thanks in advance!

 

[KOKOSEK]

And you want get results in MostRecentDate also separate by comma in case of 2 or more IDs in OrgCustID column?

 

[Cwillson]

And you want get results in MostRecentDate also separate by comma in case of 2 or more IDs in OrgCustID column?

hiya,
No, just the most recent date.

 

[KOKOSEK]

Sorry, I've missed that.

With array formula (works in xl 365):

Book1
	A	B	C	D	E
1	CustID	Date		OrgCustID	MostRecentDate
2	1	25/01/2021		1	25/01/2021
3	3	26/07/2020		3, 4, 5	26/07/2020
4	4	12/01/2021		8	01/10/2020
5	5	16/07/2020
6	8	01/10/2020
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=MAX(IF(ISNUMBER(MID(D2,ROW($1:$10),1)*1),VLOOKUP(MID(D2,ROW($1:$10),1)*1,$A$1:$B$6,2,0),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Cwillson]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Apologies for the omission! I'm using Excel 2019 desktop.

 

[Cwillson]

Sorry, I've missed that.

With array formula (works in xl 365):

Book1
	A	B	C	D	E
1	CustID	Date		OrgCustID	MostRecentDate
2	1	25/01/2021		1	25/01/2021
3	3	26/07/2020		3, 4, 5	26/07/2020
4	4	12/01/2021		8	01/10/2020
5	5	16/07/2020
6	8	01/10/2020
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=MAX(IF(ISNUMBER(MID(D2,ROW($1:$10),1)*1),VLOOKUP(MID(D2,ROW($1:$10),1)*1,$A$1:$B$6,2,0),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks Kokosek.

I'm trying to implement this in my workbook (I only provided sample data on here) but am a little perplexed as to how the ROW syntax is being used within the MID function.

 

[Fluff]

In that case, how about

+Fluff 1.xlsm
	A	B
1	CustID	Date
2	1	25/01/2021
3	3	26/07/2020
4	4	12/01/2021
5	5	16/07/2020
6	8	01/10/2020
7	1	23/01/2021
8	8	30/10/2020
Sheet1

+Fluff 1.xlsm
	A	B	C
1	OrgID	OrgCustID	MostRecentDate
2	509	1	25/01/2021
3	699	3, 4, 5	12/01/2021
4	700	8	30/10/2020
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=MAX(MAXIFS(Sheet1!$B$2:$B$10,Sheet1!$A$2:$A$10,FILTERXML("<k><m>"&SUBSTITUTE(B2,",","</m><m>")&"</m></k>","//m")))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Cwillson]

In that case, how about

+Fluff 1.xlsm
	A	B
1	CustID	Date
2	1	25/01/2021
3	3	26/07/2020
4	4	12/01/2021
5	5	16/07/2020
6	8	01/10/2020
7	1	23/01/2021
8	8	30/10/2020
Sheet1

+Fluff 1.xlsm
	A	B	C
1	OrgID	OrgCustID	MostRecentDate
2	509	1	25/01/2021
3	699	3, 4, 5	12/01/2021
4	700	8	30/10/2020
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=MAX(MAXIFS(Sheet1!$B$2:$B$10,Sheet1!$A$2:$A$10,FILTERXML("<k><m>"&SUBSTITUTE(B2,",","</m><m>")&"</m></k>","//m")))
Press CTRL+SHIFT+ENTER to enter array formulas.

Hmmm, I have named ranges for Sheet1!$B$2:$B$10 and Sheet1!$A$2:$A$10 and have substituted these in the array formula. I've updated B2 to my comma separated lookup cell, and definitely entered it as a array formula, but am getting a #VALUE! error...

 

[Fluff]

Can you post a sample of your data showing the formula that you used.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.