Find first occurence of a number in an array 7 cols wide

[Ricardo-SA]

In a lotto analysis spread sheet, I want to find row of the first occurence
of each number (1 to 49) in a 2 dimendional array 7 columns wide--essentially
to find how many spins since the last draw of the number. Can this be done
using functions only e.g. MATCH or do I need to learn VBA quickly?

 

[T. Valko]

*Maybe* something like this...

Numbers in the range A1:G100

Numbers 1:49 in the range I1:I49

Enter this array formula** in J1 and copy down to J49:

=MIN(IF(A$1:G$100=I1,ROW(A$1:G$100)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Ricardo-SA]

That is brilliant -- just what I needed -- thank you.

 

[T. Valko]

You're welcome. Thanks for the feedback!