Searching any field in array

[hviking]

I've got the following table in an array called All_Data:

Name	Device	Location	Serial	Ports	Room	Notes	Date	Time
Name1	Device1	Location1	Serial1	Ports1	Room1	Notes1	Date1	Time1
Name2	Device2	Location2	Serial2	Ports2	Room2	Notes2	Date2	Time2
Name3	Device3	Location3	Serial3	Ports3	Room3	Notes3	Date3	Time3

On a separate tab I have a search box that can return data

Search for:	I.e. Room2
Serial:	Serial2
Location	Location2

And so on. What I'm trying to accomplish is to be able to type in any field in Search for and still get the same info. Using vlookup I can get it to return one item. I.e. the field always has to be Serial.

If I use index and match it kinda works, but I guess I'm not 100% understanding how to use it as I can only get it to search on one line.

=INDEX(All_Data,MATCH("Room2",A2:I2)+1,5) would return Ports2, but again I had to specify using line 2

What would be the best way to search the whole array for any data and return the line for that data.

(Thinking out loud)

If I could search the array for say Room3, and this would return the line number, or I could add an index column and return that number, then somehow use that so show the fields in the results:

Say the search returns index number 411, then show the returns as =text(E(411)) or =text(F(411)) etc... is this possible?

Any guidance?

 

[mumps]

Do you want to search for "Room2" and return the corresponding serial and location?

 

[jasonb75]

Perhaps something like this would work?

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Device	Location	Serial	Ports	Room	Notes	Date	Time
2	Name1	Device1	Location1	Serial1	Ports1	Room1	Notes1	Date1	Time1
3	Name2	Device2	Location2	Serial2	Ports2	Room2	Notes2	Date2	Time2
4	Name3	Device3	Location3	Serial3	Ports3	Room3	Notes3	Date3	Time3
5
6	Search in:	Room	6
7	Search for:	Room2	2
8	Serial	Serial2
9	Location	Location2
Sheet2

Cell Formulas
Range	Formula
C6	C6	=MATCH($B$6,$A$1:$I$1,0)
C7	C7	=MATCH($B$7,INDEX($A$2:$I$4,0,$C$6),0)
B8:B9	B8	=INDEX($A$2:$I$4,$C$7,MATCH(A8,$A$1:$I$1,0))

Cells with Data Validation
Cell	Allow	Criteria
B6	List	=$A$1:$I$1
B7	List	=INDEX($A$2:$I$4,0,$C$6)