# MATCH function help

#### gwizrus

##### New Member
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.

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.

Hi,
Book1
ABCDEFGHI
1HostAppOSboxAboxBboxCboxBRow #
2CokeDietSolaris663
3SpriteMISAIX6
4BeerSysHP5
5GrapeMicroSolaris1
Sheet3

Formula in I2,

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

HTH

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

Replies
3
Views
220
Replies
1
Views
221
Replies
1
Views
390
Replies
3
Views
589
Replies
6
Views
191

1,219,970
Messages
6,151,215
Members
451,017
Latest member
peterlam84

### 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.

### Which adblocker are you using?

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

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