MATCH function help

[gwizrus]

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
	A	B	C	D	E	F
1	Host	App	OS	boxA	boxB	boxC
2	Coke	Diet	Solaris	6
3	Sprite	MIS	AIX		6
4	Beer	Sys	HP		5
5	Grape	Micro	Solaris			1
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]

 

[Fergus]

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]

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]

Hi,

Book1
	A	B	C	D	E	F	G	H	I
1	Host	App	OS	boxA	boxB	boxC		boxB	Row #
2	Coke	Diet	Solaris	6				6	3
3	Sprite	MIS	AIX		6
4	Beer	Sys	HP		5
5	Grape	Micro	Solaris			1
Sheet3

Formula in I2,

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

HTH

 

[gwizrus]

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