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

B

barnabyr

New Member
Joined
Mar 6, 2019
Messages
9
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:
Doc Number
Date
Company-DDA-C101_1-5000327/03/2019
Company-DDA-C101_1-6000328/11/2018
Company-DDA-C101_1-6000426/02/2019

<tbody>
</tbody>

This report is generated from a database, so I can't edit it.
In sheet2 I have:
ModelDate
Company-DMA-C101_1-5000127/03/2019
Company-DMA-C101_1-6000128/11/2019
Company-DMA-C101_1-6000228/11/2019
Company-DMA-C101_1-6000328/11/2019

<tbody>
</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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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
ABC
2Doc NumberDate
3Company-DDA-C101_1-500033/27/19
4Company-DDA-C101_1-6000311/28/18
5Company-DDA-C101_1-600042/26/19
6
7ModelFind ThisDate
8Company-DMA-C101_1-50001C101_1-503/27/19
9Company-DMA-C101_1-60001C101_1-602/26/19
10Company-DMA-C101_1-60002C101_1-602/26/19
11Company-DMA-C101_1-60003C101_1-602/26/19
Sheet16
Cell Formulas
RangeFormula
C8=AGGREGATE(14,6,IF(SEARCH(B8,$A$3:$A$5),$B$3:$B$5),1)
<strike>
</strike>[/FONT]​
 
Upvote 0
DRSteele said:
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.

ABC
2Doc NumberDate
3Company-DDA-C101_1-500033/27/19
4Company-DDA-C101_1-6000311/28/18
5Company-DDA-C101_1-600042/26/19
6
7ModelFind ThisDate
8Company-DMA-C101_1-50001C101_1-503/27/19
9Company-DMA-C101_1-60001C101_1-602/26/19
10Company-DMA-C101_1-60002C101_1-602/26/19
11Company-DMA-C101_1-60003C101_1-602/26/19

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet16​

Worksheet Formulas
CellFormula
C8=AGGREGATE(14,6,IF(SEARCH(B8,$A$3:$A$5),$B$3:$B$5),1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

<strike>
</strike>​
Click to expand...

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?
 
Upvote 0
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?
 
Upvote 0
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
ABC
2Doc NumberDate
3Company-DDA-C101_1-5000327/03/2019
4Company-DDA-C101_1-6000328/11/2018
5Company-DDA-C101_1-6000426/02/2019
6
7Modelfind thisDate
8Company-DMA-C101_1-50001C101_1-5027/03/2019
9Company-DMA-C101_1-60001C101_1-6026/02/2019
10Company-DMA-C101_1-60002C101_1-6026/02/2019
11Company-DMA-C101_1-60003C101_1-6026/02/2019
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
FormR said:
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
ABC
2Doc NumberDate
3Company-DDA-C101_1-5000327/03/2019
4Company-DDA-C101_1-6000328/11/2018
5Company-DDA-C101_1-6000426/02/2019
6
7Modelfind thisDate
8Company-DMA-C101_1-50001C101_1-5027/03/2019
9Company-DMA-C101_1-60001C101_1-6026/02/2019
10Company-DMA-C101_1-60002C101_1-6026/02/2019
11Company-DMA-C101_1-60003C101_1-6026/02/2019

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



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.
Click to expand...

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?
 
Upvote 0
barnabyr said:
Can you tell me how the ISNUMBER function works in this instance please?
Click to expand...

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.
 
Upvote 0
FormR said:
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.
Click to expand...

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?
 
Upvote 0
Hi, yes, that's or more or less it :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Question
Need to pull a min or max date from multiple criteria
Replies
8
Views
313
KRice
K
E
  • Solved
Index/Match - Return value if partial text from cell contained anywhere in separate column
Replies
5
Views
2K
excelnewb221
E
C
  • Solved
Search multiple columns for value, but within date range, return values of other column
Replies
6
Views
503
Fluff
Fluff
skyh3ck
  • Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
292
skyh3ck
skyh3ck
A
  • Solved
Lookup: If a cell contains a string, return a value from an array
Replies
5
Views
10K
topi1
T

Forum statistics

Threads
1,215,339
Messages
6,124,370
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top