Match Last Occurrence of two numbers and Return Date

[Sam via OfficeKB.com]

Hi All,

Thank you very much for taking the time to provide various formulas.

I've received assistance with a similar scenario that was based on Counting
and Summing the relevant occurances of two specific numbers.

This time, I need to find /match two specific numbers that appear together
and return the Date of their LAST occurrence together.

I have a table of numbers that spans nine Columns (A-I)and 400+ Rows (20-
480). I have made the table a Dynamic Range called "Numbers". Each Row
contains numbers in ascending order. The Date is contained in a single
Column (J) - Dynamic Range called "Date".

Is there a formula that can check for two specific numbers Row by Row
through the (nine column) Range "Numbers" and Return the Date of their LAST
appearance together, from the Dynamic Range "Date"?

Column J = Dynamic Range "Date"
Columns A-I = Dynamic Range "Numbers"
Rows 20-480

Example sample data from Range "Numbers":

ROW20 51 58 59 65 69 72 73 76 79
ROW31 50 51 58 72 73 76 79 80 81
ROW50 50 52 60 62 68 69 70 75 76
ROW75 53 54 59 60 62 69 70 72 73
ROW80 50 51 58 59 70 71 72 73 76
ROW83 51 53 65 67 68 69 78 80 81
ROW94 51 52 58 60 61 65 67 72 73
ROW99 50 53 57 62 63 68 70 71 73

Example Criteria:
Find the LAST occurrence of 72 AND 73 together and return the Date.

Regards,
Sam

 

[Domenic]

Try...

=INDEX(Date,MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Number
s)),0,1),72)*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),7
3))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much for your time and assistance. Your formula does return
the desired result.

Thanks,
Sam

PS. Is there a book that you could recommend to assist with learning to put
together various Function syntax's as in your working formula - appeciated.

Sam

 

[Sam via OfficeKB.com]

Hi Domenic,

If you can spare the time, would you mind explaining what each part of the
formula is doing.

Your Formula:
=INDEX(Date,MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers))
,0,1),72)*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73))))

....confirmed with CONTROL+SHIFT+ENTER.

Thanks again for your help.
Sam

 

[Domenic]

Let's assume that A1:J5 contains the following table...

51 58 59 65 69 72 73 76 79 Jan-05
50 51 58 72 73 76 79 80 81 Feb-05
50 52 60 62 68 69 70 75 76 Mar-05
53 54 59 60 62 69 70 72 75 Apr-05
50 51 58 59 70 71 72 73 76 May-05

....and that A1:I5 is defined as 'Numbers', and J1:J5 is defined as Date,
the following...

=INDEX(Date,MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Number
s)),0,1),72)*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),7
3))))

....can be broken down as follows...

ROW(Numbers)-MIN(ROW(Numbers)) returns the following array of values...

{0;1;2;3;4}

This array of numbers is used as the second argument of the OFFSET
function. Therefore...

OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1) returns the following
array of ranges...

A1:I1
A2:I2
A3:I3
A4:I4
A5:I5

This array of ranges, in turn, is used by both COUNTIF functions...

COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),72) returns
the following array of values...

{1;1;0;1;1}

COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73) returns
the following array of values...

{1;1;0;0;1}

When we combine these two COUNTIF functions...

(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),72)*COUNTIF(O
FFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73)) returns the
following array of values...

{1;1;0;0;1}

1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),72)*COUNTIF
(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73)) returns the
following array of values...

{1;1;#DIV/0!;#DIV/0!;1}

MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),72)
*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73)))
returns 5 and is used as an argument for the INDEX function to return
'May-05'.

Note that 1 divided by a number greater than or equal to 0 will always
equal a number less than or equal to 1, except where you divide by 0, in
which case you get #DIV/0!. So when you have...

MATCH(2,1/(COUNTIF(...)*COUNTIF(...)))

....MATCH ignores the #DIV/0! error values in the array of values
returned by 1/(COUNTIF(...)*COUNTIF(...)) and returns the position of
the last numerical value in that array.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much for your very detailed and extremely helpful
explanation of your formula. Your time and help is very much appreciated.

=INDEX(Date,MATCH(2,1/(COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers))
,0,1),72)*COUNTIF(OFFSET(Numbers,ROW(Numbers)-MIN(ROW(Numbers)),0,1),73))))

....confirmed with CONTROL+SHIFT+ENTER.


Thanks
Sam

 

[Daniel.M]

Hi,

Also, with your data in A1:I8:
=INDEX(Dates,MATCH(2,1/MMULT((A1:H8=72)*(B1:I8=73),{1;1;1;1;1;1;1;1})))

For dynamic ranges, you can have a 7 column-wide (instead of 7) dynamic area
named : Num7
and a second one named Num7Right
=OFFSET(Num7,0,1)

Then, the formula becomes:
=INDEX(Dates,MATCH(2,1/MMULT((Num7=72)*(Num7Right=73),{1;1;1;1;1;1;1;1})))

Regards,

Daniel M.