Excel lookup data, help please. 2

[DECNET]

I have an Excel spreadsheet that contains in Column A dates for the current year, in columns B to G are numbers between 1 and 49 (Lottery numbers) depending on the drawn numbers for the corresponding date in that Row.
What I want to do is, on another Worksheet, determine the last date on which a certain number was drawn, ie report back the date from Column A where the required number appears in the same Row, regardless of Column.
Obviously, there are going to be repetitions of numbers in the Rows, what I need to find is the LAST instance of that number in the array and report back the corresponding date from Column A.

I hope I've explained that sufficiently.

Over to you Excel guru's........ DEC

 

[JVFriederick]

How does a 5% commission on winnings sound to you?

Dates in column A, numbers in B:G, with 5 records (dates) so far.

In cell A10,A11,A12 etc enter 1,2,3 and so on for each possible number. Then in cell B10, enter the following formula:

=MAX((($B$1:$G$5)=$A10)*A$1:A$5)

Use Ctrl+Shift+Enter to enter the matrix array formula so it looks like this inside the cell:

{=MAX((($B$1:$G$5)=$A10)*A$1:A$5)}

Copy this formula down for all possible numbers to determine the last date matching each number.

 

[SkipVought]

Hi,
I took a different approch...

1. In I9 I am entreing the Number you want to look for

2. Put this formula in J - O (copy into J and drag across)
=IF(ISERROR(MATCH($I9,B:B,0)),99,MATCH($I9,B:B,0))

3. Put this formula in like I10
=INDEX(Date,MIN(J9:O9)-1,1)
It is a date.

4. Sort your table in descending order by Date (Col A)

Can I get a cut, too???
Skip,
metzgsk@voughtaircraft.com

 

[JVFriederick]

I don't see why not ;-)

 

[SkipVought]

JVF,

Let's figger out how to spend R winins!!!!! X-) Skip,
metzgsk@voughtaircraft.com

 

[DECNET]

Thanks to you both, at my next oppurtunity I will try both of your solutions, the best one gets the cut of my Jackpot win!!! (You may have a long wait though). I will let you know how I get on.

Cheers DEC

 

[DECNET]

I have tried JVFriederick's approach and it's exactly what I need. Many thanks.
I will also try your solution Skip and let you know. DEC