Lookup Values, etracting Row header and column header.

[visgenesis]

Hi all,

I have a dat matrix having 100 rows and 100 columns.

The data is dynamic and keeps changing.

3500 3550 3600 3650 3700
3550 - - - -
3600 - - - -
3650 - - - -
3700 - - - -
3750 - - - -
3800 - 20 - -
3850 - - - -
3900 - - - 15

For eg: the data in row 3800 and column 3600 shows a value of 20.

Is there any function by which i can extract any positive values in
the data matrix such that it gives a summary in another worksheet:


# Row Column Value
1 3800 3600 20
2 3900 3700 15

Note: the values in the matrix changes based on data received from
external source using web query. (i.e) any time the nil value data can
change positive and that data should appear in the summary sheet.


Regards
Vishnu

 

[Billy Liddel]

Vishnu

The following works on the data you provided, just change the ranges to
suit. I gave the matrix the name Table.

=INDEX(Table,MATCH(H2,$A$1:$A$9,0),MATCH(I2,$A$1:$E$1,0))

# H1 H1 Formula
1 3800 3600 20
2 3900 3700 15

First MATCH finds the row number, second MATCH finds the column number and
INDEX show the results of the intersection.

Regards
Peter