Finding Multiple Matches and their values

[FLYNNE]

Operating System - Windows 7 Home Premium 64 bit
Excel Version - Excel 2007

My problem concerns the uploaded file Functions Test .
The functions tested include VLOOKUP, INDEX MATCH,COUNTIF and MAX.
When I wish to familiarise myself with various functions I create suitable tables of numbers and try to apply the relevant formulae.
Functions Test contains the following :-
TABLE 1 on the extreme left has 30 rows each containing Lottery results.
TABLE 2 contains the frequency of each number occurring in the whole of TABLE 1.
VLOOKUP is used in this table to match each frequency to its appropriate number in TABLE 1 using the NUMBER - FRQUENCY table on the right of the file.
TABLE 3 contains numbers in TABLE 1 which have the maximum frequency in a particular row . eg the numbers which have a maximum frequency in row one in TABLE 1
are 12 and 34.
As you can see I have used a formula using INDEX , MATCH and MAX to obtain these numbers with maximum frequency per row.
Unfortunately only one of these numbers is shown for each row.
Is there any formula sequence which will display all numbers in a row with maximum frequency and arrange these values in a row (not a column) ?
Many forums suggest suitable answers but arranging the results in columns not rows.
I hope I have made my problem clear enough .
Please advise.
Regards
EFlynn

[tamthat]

z1=lookup(2,1/((large($s1:$w1*1000-column($s1:$w1),column(a1)))=$s1:$w1*1000-column($s1:$w1)),$l1:$p1) 

[FLYNNE]

Hello Tamthat

Thank you very much for your ingenious answer - it readily solves my problem.

I am intrigued by your formula.

Could you possibly explain how the formula operates and why 1000 is used ?

In cell Z1 the formula refers to COLUMN (A1) - A1 is usually a cell reference but in my worksheet A1 is blank and is hidden.

Thanks once again - I much appreciate it.

Regards

Flynne