MATCH function help

gwizrus

New Member
Joined
Sep 26, 2006
Messages
12
I have a ss with different tabs. I'm retrieving information from another tab called Servers to populate another tab based on certain search criteria. The tab I'm collecting data from looks similar to this:
Book1
ABCDEF
1HostAppOSboxAboxBboxC
2CokeDietSolaris6
3SpriteMISAIX6
4BeerSysHP5
5GrapeMicroSolaris1
Sheet1


I want to be able to pull data from a given row that matches a search criteria. For instance, lets say I'm searching for boxB with the number in the macthing column that contains a 6. this means I want data from row 3.

I'm able to find the column based on a unique name (boxB) populated in M32 by using MATCH(M32,Servers!$1:$1,0). Column found = 5. Now, how do I find row number? I don't know how to tell the function to look in column 5 to find a number 6 to give me the row number.

I planned on using the row and col in the index function to retrieve the data.

How do I find the row or if there is an easier way, please let me know.

Thanks[/img]
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
lets say I'm searching for boxB with the number in the macthing column that contains a 6. this means I want data from row 3.
In your example the first instance of a 6 in the fourth column is in row 2 not 3. Do you want just the first instance or all?
I don't know how to tell the function to look in column 5 to find a number 6 to give me the row number
The short answer to this is: =MATCH(6,E:E,0) but see query above.

Edit, now I see you have edited your post to put the 6 in the fifth column.
 

gwizrus

New Member
Joined
Sep 26, 2006
Messages
12
Yes, I edited it by capturing the excel output for better clarification. I need to first find the column with either boxA, boxB or boxC. Then I search based on a number to find the row in that column. Each of the box_ columns contain unique numbers within the given column.

If I'm searching for boxB and the number 6 it falls in column 5 and row 3. My goal is to use those coordinates (row and col) to insert into the index function to retrieve data on row 3.
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,
Book1
ABCDEFGHI
1HostAppOSboxAboxBboxCboxBRow #
2CokeDietSolaris663
3SpriteMISAIX6
4BeerSysHP5
5GrapeMicroSolaris1
Sheet3


Formula in I2,

=MATCH(H2,INDEX(A1:F5,,MATCH(H1,A1:F1,0)),0)

HTH
 

gwizrus

New Member
Joined
Sep 26, 2006
Messages
12
Kris,

Thanks a bunch. It works!!! I broke the entire line up.

=MATCH(H2,INDEX(A1:F5,,MATCH(H1,A1:F1,0)),0)

How is excel interpreting the missing row in the INDEX function. I'm new to excel and trying to get a grasp on the order it computes the statement.

Thanks
 

Forum statistics

Threads
1,141,152
Messages
5,704,594
Members
421,358
Latest member
Redstar13

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