Excel.. find value in a 2D array

[ceecld]

Hi im looking to find the cell ref of a particular number in an array, e.g.
A B C
--------
1| x y b
2| l m j
3| h i e

so in this simple example i might want ot know where "m" is located... B2

Im having trouble coming up with a formula to find this cell reference. Note that my arrays are 100x700.

Cheers

 

[GlennUK]

So you have 70000 unique numbers then?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[ceecld]

70,000 yep... its a sparse matrix.. lots of zeros but all other values are unique

 

[GlennUK]

Row where item is: =SUMPRODUCT((A1:C3="m")*(ROW(A1:C3)))
Col where item is: =SUMPRODUCT((A1:C3="m")*(COLUMN(A1:C3)))

Does that help?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[firefytr]

Or, combine the two to return an address..

=ADDRESS(SUMPRODUCT((A1:C3="m")*(ROW(A1:C3))),SUMPRODUCT((A1:C3="m")*(COLUMN(A1:C3))))

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[ceecld]

Cheers guys!

 

[yogia]

Hi ceecld:

If you have more than 1 entry of interest, then you may want to use ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com