I am trying to create a list of cables in a cable rack. Thus each cable is
linking two points A & B. I have created three columns as follows

A B C
Cable# Port A Port B
0001 F/1 F/2
0002 F/3 F/4

and so on...

Now I want to create a search to find out which cable is connected to port
F/2 for instance. As you can see here I have to search both columns B&C to
find the answer, and i am lost on an easier way of representing the data, or
doing the search.
--
Hany ElKady
IT Consultant

Put the number in ColumnA that you are looking for in Cell E1. Then, put
this function in Cell E2:
=IF(INDEX(B1:B12,MATCH(1,--EXACT(A1:A12,E1),0))0,INDEX(B1:B12,MATCH(1,--EXACT(A1:A12,E1),0)),INDEX(C1:C12,MATCH(1,--EXACT(A1:A12,E1),0)))

Commit with Ctrl+Shift+Enter (not just enter).

Regards,
Ryan---

--
RyGuy


"Hany ElKady" wrote:

I am trying to create a list of cables in a cable rack. Thus each cable is
linking two points A & B. I have created three columns as follows

A B C
Cable# Port A Port B
0001 F/1 F/2
0002 F/3 F/4

and so on...

Now I want to create a search to find out which cable is connected to port
F/2 for instance. As you can see here I have to search both columns B&C to
find the answer, and i am lost on an easier way of representing the data, or
doing the search.
--
Hany ElKady
IT Consultant

Hi Thanks for the answer; it is not quite working though...

The match part gives an error if it does not find the item in the column
(which it will do for one of those columns) i had to use iferror to fix that
(is there a better way). Also, what does the -- stand for in the formula ?

--
Hany ElKady
Professional Services Architect
Technology & Service Delivery


"ryguy7272" wrote:

Put the number in ColumnA that you are looking for in Cell E1. Then, put
this function in Cell E2:
=IF(INDEX(B1:B12,MATCH(1,--EXACT(A1:A12,E1),0))0,INDEX(B1:B12,MATCH(1,--EXACT(A1:A12,E1),0)),INDEX(C1:C12,MATCH(1,--EXACT(A1:A12,E1),0)))

Commit with Ctrl+Shift+Enter (not just enter).

Regards,
Ryan---

--
RyGuy


"Hany ElKady" wrote:

I am trying to create a list of cables in a cable rack. Thus each cable is
linking two points A & B. I have created three columns as follows

A B C
Cable# Port A Port B
0001 F/1 F/2
0002 F/3 F/4

and so on...

Now I want to create a search to find out which cable is connected to port
F/2 for instance. As you can see here I have to search both columns B&C to
find the answer, and i am lost on an easier way of representing the data, or
doing the search.
--
Hany ElKady
IT Consultant

A2 contains the Port ID you are searching for.
Data starts in A5 (using columns A:C)
D5 contains the formula: =OR(B5=$A$2,C5=$A$2)
D5 also contains Conditional Formatting, using the formula: =D5=TRUE

Fill the formula in column D down alongside your data.
If you put titles over your data in row 4, then you will be able to
use the AutoFilter to display all of the found items (col 4 will equal true).
--
Jim Cone
Portland, Oregon USA



"Hany ElKady"

wrote in message
I am trying to create a list of cables in a cable rack. Thus each cable is
linking two points A & B. I have created three columns as follows

A B C
Cable# Port A Port B
0001 F/1 F/2
0002 F/3 F/4

and so on...
Now I want to create a search to find out which cable is connected to port
F/2 for instance. As you can see here I have to search both columns B&C to
find the answer, and i am lost on an easier way of representing the data, or
doing the search.
--
Hany ElKady
IT Consultant