Function to look up a text string in multiple cells of a column & return the max value from a corresponding date column

[barnabyr]

Hi, I have gone round & round in circles trying to find a solution to this.
In sheet1 I have a bunch of document names & the dates that the documents were revised like this:
[TABLE="width: 284"]
<tbody>[TR]
[TD]Doc Number
[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Company-DDA-C101_1-50003[/TD]
[TD="align: right"]27/03/2019[/TD]
[/TR]
[TR]
[TD]Company-DDA-C101_1-60003[/TD]
[TD="align: right"]28/11/2018[/TD]
[/TR]
[TR]
[TD]Company-DDA-C101_1-60004[/TD]
[TD="align: right"]26/02/2019[/TD]
[/TR]
</tbody>[/TABLE]

This report is generated from a database, so I can't edit it.
In sheet2 I have:

Company-DMA-C101_1-50001

Company-DMA-C101_1-60001

Company-DMA-C101_1-60002

Company-DMA-C101_1-60003

<tbody>
[TD="class: xl1084"]Model[/TD]
[TD="class: xl1086"]Date[/TD]

[TD="class: xl1085, align: right"]27/03/2019[/TD]

[TD="class: xl1085, align: right"]28/11/2019[/TD]

[TD="class: xl1085, align: right"]28/11/2019[/TD]

[TD="class: xl1085, align: right"]28/11/2019[/TD]

</tbody>

What I would like is to find a formula to go in the date column of sheet2 that returns the latest (max) date from sheet 1 in rows that contain the text string "*C101_1-60*.
The text string is extracted from the cells in the model column on sheet2.
I'm currently using an index with match function but as you can see it's just returning the first row from the match function rather than the max date from rows 2&3 in sheet1.
I have tried using a max function with if cell range equals text but it doesn't like the wildcards.

Sorry for how lengthy this post is but I wanted to put as much information as possible.

 

[DRSteele]

Welcome to the forum.

Let's see if this will help. I think your example should show a maximum Date for

C101_1-60 as Feb 26/19; right? As you said, function MATCH will only find the first instance.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C
2	Doc Number	Date
3	Company-DDA-C101_1-50003	3/27/19
4	Company-DDA-C101_1-60003	11/28/18
5	Company-DDA-C101_1-60004	2/26/19
6
7	Model	Find This	Date
8	Company-DMA-C101_1-50001	C101_1-50	3/27/19
9	Company-DMA-C101_1-60001	C101_1-60	2/26/19
10	Company-DMA-C101_1-60002	C101_1-60	2/26/19
11	Company-DMA-C101_1-60003	C101_1-60	2/26/19
Sheet16

Cell Formulas
Range	Formula
C8	=AGGREGATE(14,6,IF(SEARCH(B8,$A$3:$A$5),$B$3:$B$5),1)

_{^{<strike>
</strike>}}[/FONT]​

 

[barnabyr]

Welcome to the forum.

Let's see if this will help. I think your example should show a maximum Date for

C101_1-60 as Feb 26/19; right? As you said, function MATCH will only find the first instance.

	A	B	C
Company-DDA-C101_1-50003
Company-DDA-C101_1-60003
Company-DDA-C101_1-60004
Company-DMA-C101_1-50001	C101_1-50
Company-DMA-C101_1-60001	C101_1-60
Company-DMA-C101_1-60002	C101_1-60
Company-DMA-C101_1-60003	C101_1-60

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: #FFF2CC"]Doc Number[/TD]
[TD="bgcolor: #FFF2CC"]Date[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3/27/19[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]11/28/18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2/26/19[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FCE4D6"]Model[/TD]
[TD="bgcolor: #FCE4D6"]Find This[/TD]
[TD="bgcolor: #FCE4D6"]Date[/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3/27/19[/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2/26/19[/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2/26/19[/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2/26/19[/TD]

</tbody>

Sheet16​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"]=AGGREGATE(14,6,IF(SEARCH(B8,$A$3:$A$5),$B$3:$B$5),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

_{^{<strike>
</strike>}}​

Thanks very much for your quick reply!
I can't seem to get this to work, I keep getting the #VALUE ! error.
When I evaluate the formula it appears that the search function can't find the text string in the data range.
Also, I can't really add cells to contain the text string as this is in a supplied template, so I have been using "*"&(LEFT(MID(B1597,17,100),LEN(MID(B1597,17,100))-3))&"*" to get the text string within the formula.
Does the aggregate function handle this kind of string?

 

[barnabyr]

Actually, even when I paste your example into a fresh sheet, it still doesn't work!
Does the aggregate function require some kind of add-in or something?

 

[FormR]

Hi, it looks to me like the formula in post2 requires confirming with CTRL+SHIFT+ENTER.

Here is an alternative you can try along simlar lines:

Excel 2013/2016
	A	B	C
2	Doc Number	Date
3	Company-DDA-C101_1-50003	27/03/2019
4	Company-DDA-C101_1-60003	28/11/2018
5	Company-DDA-C101_1-60004	26/02/2019
6
7	Model	find this	Date
8	Company-DMA-C101_1-50001	C101_1-50	27/03/2019
9	Company-DMA-C101_1-60001	C101_1-60	26/02/2019
10	Company-DMA-C101_1-60002	C101_1-60	26/02/2019
11	Company-DMA-C101_1-60003	C101_1-60	26/02/2019
Sheet1

Cell Formulas
Range	Formula
C8	=AGGREGATE(14,6,$B$3:$B$5/ISNUMBER(SEARCH(B8,$A$3:$A$5)),1)

You can replace the B8 references with a formula that extracts the lookup string - note, that you do not need to include the wild card characters (*).

If you are using a newer version of Excel you could also consider looking at using the MAXIFS() function with wildcard characters.

 

[barnabyr]

Hi, it looks to me like the formula in post2 requires confirming with CTRL+SHIFT+ENTER.

Here is an alternative you can try along simlar lines:

Excel 2013/2016

	A	B	C
Doc Number	Date
Company-DDA-C101_1-50003
Company-DDA-C101_1-60003
Company-DDA-C101_1-60004
Model	find this	Date
Company-DMA-C101_1-50001	C101_1-50
Company-DMA-C101_1-60001	C101_1-60
Company-DMA-C101_1-60002	C101_1-60
Company-DMA-C101_1-60003	C101_1-60

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]27/03/2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]28/11/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]26/02/2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]27/03/2019[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]26/02/2019[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]26/02/2019[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]26/02/2019[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"]=AGGREGATE(14,6,$B$3:$B$5/ISNUMBER(SEARCH(B8,$A$3:$A$5)),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



You can replace the B8 references with a formula that extracts the lookup string - note, that you do not need to include the wild card characters (*).

If you are using a newer version of Excel you could also consider looking at using the MAXIFS() function with wildcard characters.

FormR! That's the answer! Thanks DRSteele & FormR for your help.
I had to wrap it in an IFERROR for where the text string doesn't appear in the data range but that solved it.
I haven't used the AGGREGATE function before, it looks frantically helpful.

Can you tell me how the ISNUMBER function works in this instance please?
I understand SEARCH looks for the text string in the data range but I'm not sure I understand how that combines with ISNUMBER & then how AGGREGATE evaluates all that?

 

[FormR]

Can you tell me how the ISNUMBER function works in this instance please?

Hi, sure - ISNUMBER() will return TRUE when the SEARCH() function finds a match and FALSE when it doesn't.

In Excel TRUE evaluates to the number 1 and FALSE evaluates to 0 - so we end up dividing each cell in B3:B5 by 1 when there is a match or 0 when there isn't. The divide by zeros return a DIV/0 error which the AGGREGATE() function ignores because we have used "6" in the options argument.

 

[barnabyr]

Hi, sure - ISNUMBER() will return TRUE when the SEARCH() function finds a match and FALSE when it doesn't.

In Excel TRUE evaluates to the number 1 and FALSE evaluates to 0 - so we end up dividing each cell in B3:B5 by 1 when there is a match or 0 when there isn't. The divide by zeros return a DIV/0 error which the AGGREGATE() function ignores because we have used "6" in the options argument.

Got you, so:
SEARCH returns a list of cells in the document range & whether or not each of those cells match the text string.
ISNUMBER turns that list into a list of either 1 & 0.
AGGREGATE then divides the date range by the ISNUMBER range, the errors are ditched & the largest value in the remaining list is returned.

Is that correct?

 

[FormR]

Hi, yes, that's or more or less it

 

[barnabyr]

Hi, yes, that's or more or less it

Perfect, thanks very much for your help.