A question about searching for data in a 2-d matrix

[Kael_Moser]

Ok. I have a table of data, a 2D matrix if you will. I am trying to find the easiest way to write a function to search this matrix for a particular value. However, the thing that has stopped me is that I don't know how to search a particular column of the chart.

I start off with a cell that has the value I am searching for, and a cell that holds a value representing which column to search fo r it in. I want to know what row the value is found in. How would I do it?

Thanks for any help you can give.

 

[PaddyD]

multi key lookups are failry straightforward - care to post examples of the data you've got, where it is on the sheet etc

paddy

 

[Kael_Moser]

Ok. Let me see if I can.

1 2 3 4 5 6 7
-4 .5
-3 .5 .5 1
-2 .5 1 .5 1 2
-1 .5 1 2 .5 1 2 4
0 1 2 4 1 2 4 8
1 2 4 8 2 4 6 12
2 4 8 16 4 6 8 16
3 8 16 32 6 8 10 20

One cell holds the number above the column.
Another cell holds a number to search for in that column. I want the number at the left-most side of the table, the row number.

For instance, Cell 1 holds 5, Cell 2 holds 4. I want the answer of 1 to be returned.

Sorry if it sounds rediculously simple, but this is my first time tangling with Excel.

 

[Kael_Moser]

Bleh. Looks liek my attempt to make a text based table failed miserably. I'll make a screen capture and post it after I get home.

 

[PaddyD]

<pre>


1 2 3 4 5 6 7
-4 .5
-3 .5 .5 1
-2 .5 1 .5 1 2
-1 .5 1 2 .5 1 2 4
0 1 2 4 1 2 4 8
1 2 4 8 2 4 6 12
2 4 8 16 4 6 8 16
3 8 16 32 6 8 10 20


</pre>

 

[PaddyD]

with the data you posted in cells a1:h9, and the values as follows:

c12 = 5
c13 = 4

try this:

=INDEX(A2:A9,MATCH(C13,OFFSET(A2:A9,0,C12)),0)

which will only work if the column headings are numbers 1 thro' 7.

paddy

 

[PaddyD]

Book1
	A	B	C	D	E	F	G	H
1		1	2	3	4	5	6	7
2	-4							0.5
3	-3		.	5			0.5	1
4	-2		0.5	1		0.5	1	2
5	-1	0.5	1	2	0.5	1	2	4
6	0	1	2	4	1	2	4	8
7	1	2	4	8	2	4	6	12
8	2	4	8	16	4	6	8	16
9	3	8	16	32	6	8	10	20
10
11
12			5		1
13			4
Sheet3

 

[Kael_Moser]

Thanks. I'll give it a try.