Searching any field in array

hviking

Board Regular
Joined
Mar 31, 2002
Messages
59
I've got the following table in an array called All_Data:

NameDeviceLocationSerialPortsRoomNotesDateTime
Name1Device1Location1Serial1Ports1Room1Notes1Date1Time1
Name2Device2Location2Serial2Ports2Room2Notes2Date2Time2
Name3Device3Location3Serial3Ports3Room3Notes3Date3Time3

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

Search for:I.e. Room2
Serial:Serial2
LocationLocation2

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?
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Do you want to search for "Room2" and return the corresponding serial and location?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows
Perhaps something like this would work?

Book1
ABCDEFGHI
1NameDeviceLocationSerialPortsRoomNotesDateTime
2Name1Device1Location1Serial1Ports1Room1Notes1Date1Time1
3Name2Device2Location2Serial2Ports2Room2Notes2Date2Time2
4Name3Device3Location3Serial3Ports3Room3Notes3Date3Time3
5
6Search in:Room6
7Search for:Room22
8SerialSerial2
9LocationLocation2
Sheet2
Cell Formulas
RangeFormula
C6C6=MATCH($B$6,$A$1:$I$1,0)
C7C7=MATCH($B$7,INDEX($A$2:$I$4,0,$C$6),0)
B8:B9B8=INDEX($A$2:$I$4,$C$7,MATCH(A8,$A$1:$I$1,0))
Cells with Data Validation
CellAllowCriteria
B6List=$A$1:$I$1
B7List=INDEX($A$2:$I$4,0,$C$6)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,585
Messages
5,625,654
Members
416,124
Latest member
DeMoNloK

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