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

Kael_Moser

New Member
Joined
Oct 29, 2002
Messages
4
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Oct 29, 2002
Messages
4
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

New Member
Joined
Oct 29, 2002
Messages
4
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

<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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Book1
ABCDEFGH
11234567
2-40.5
3-3.50.51
4-20.510.512
5-10.5120.5124
601241248
7124824612
82481646816
9381632681020
10
11
1251
134
Sheet3
 

Forum statistics

Threads
1,143,640
Messages
5,719,987
Members
422,257
Latest member
Calion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top