How can I create a list of links (A to B) & search for both A & B

H

Hany ElKady

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.
 
R

ryguy7272

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---
 
H

Hany ElKady

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 ?
 
J

Jim Cone

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"
<[email protected]>
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Resize a row but copy but skip columns I don't want to copy 6
How to exchange data? 4
how do I find all the rows containing a specific value in excel 1
Copy ActiveWorksheet Columns B,C,F to Another Worksheet in Workboo 1
Duplicate in A copy value in B (loop?) 2
A simple if <> nn then Msgbox "No Match" 5
Help needed to writing a macro to put the year in column B of a tableusing Table nomenclature & the 7
Creating xla file to clear junk and sort reqd data 4

Top