Ok, you have to find both the tag number and the device?

Let's assume the setup is such:

Sheet1

A2 = 123456
B2 = Solenoid Valve

Sheet2

D2:E4 =

Conveyor P207 Solenoid Valve 123456 Junction Box...060-E-12345
Conveyor P207 Local Control Station Solenoid Valve 123456...060-E-12345
Motor Control Centre 243567...061-E-2456

Enter this array formula** on Sheet1 C2:

=INDEX(Sheet2!E2:E4,MATCH(1,(ISNUMBER(SEARCH(A2,Sh eet2!D2:D4)))*(ISNUMBER(SEARCH(B2,Sheet2!D2:D4))), 0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"VickiMc" wrote in message
...
A colleague has asked for help producing a formula and it has me stumped.
What she has is two files, one contains a list of devices where column A
contains the TAG Number of the Device, and Column B contains a Description
of
the Device.
The Second File is a 'Cabling Schedule' where column D (may or may not)
contain the Description of a Device, and could contain more information
about
it than the Device File. Column E of this File contains the number of the
Cable Schedule/ Drawing, that my colleague wants returned to the Device
File
once a match is found.
EG: (sample) Device File
(A) 123456 (B) Solenoid Valve
(A) 234567 (B) Solenoid Valve
EG (sample) Cable Schedule
(D) Conveyor P207 Solenoid Valve 123456 Junction Box (E) 060-E-12345
(D) Conveyor P207 Local Control Station Solenoid Valve 123456 (E)
060-E-12345
(D) Motor Control Centre 243567 (E) 061-E-2456

Thing is, there is no consistancy to the character length of the TAG
number,
nor is there any specific location where the TAG number may be contained
in
the text of Col (D) in the second of the two files, and once it is
located,
the contents of Column E are to be placed in Column C of the Device File.