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]
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

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
 

Watch MrExcel Video

Forum statistics

Threads
1,111,491
Messages
5,541,034
Members
410,542
Latest member
GDiddy1984
Top