Look Up Function

[Alec Kolundzic]

Help

On a sheet called routing, I have a number of cables, a1-a7, routed on
traywork, T1-T6. This gives me the routing for each cable. But on another
sheet called population, I need to see what each tray contains.

See following:

Routing
a1 T1 T2 T3 T4 T5 T6
a2 T1 T3 T4 T5 T6
a3 T1 T3 T4 T5 T6
a4 T1 T5 T6
a5 T1 T2 T3 T4 T5 T6
a6 T1 T2 T5 T6
a7 T1 T5 T6


Population
T1 T2 T3 T4 T5 T6
a1 a1 a1 a1 a1 a1 a1
a2 a2 a2 a2 a2 a2
a3 a3 a3 a3 a3 a3
a4 a4 a4 a4
a5 a5 a5 a5 a5 a5 a5
a6 a6 a6 a6 a6
a7 a7 a7 a7

I've tried Look Up functions with little success, any ideas.

Thanks
Alec Kolundzic

 

[Tom Hayakawa]

Howdy,

Let's assume your Routing table resides in cells A1 to G7.

Also assume your Population table resides in cells A9 to G16.

Try this formula in cell B10: =IF(COUNTIF($A1:$G1,B$9)>0,$A10,"")

Replicate it across your Population table (maintaining the proper cell
ranges, of course), and it should give you what you want. Of course, this
formula assumes a cable will only ever be in one tray a maximum of one time.
Also, you could make the cable runs Named Ranges and simplify the formula
somewhat.

Good luck,

Tom Hayakawa

 

[Alec Kolundzic]

Thanks Tom,
This works just fine.

Howdy,

Let's assume your Routing table resides in cells A1 to G7.

Also assume your Population table resides in cells A9 to G16.

Try this formula in cell B10: =IF(COUNTIF($A1:$G1,B$9)>0,$A10,"")

Replicate it across your Population table (maintaining the proper cell
ranges, of course), and it should give you what you want. Of course, this
formula assumes a cable will only ever be in one tray a maximum of one time.
Also, you could make the cable runs Named Ranges and simplify the formula
somewhat.

Good luck,

Tom Hayakawa