look for a pipeID in a column and returns "broken" result

[Khoshravan]

I have a column of pipeID (1400 IDs).
I have a column of Target pipes. (78pipe IDs)
I want a command to look Target pipes in pipeID and writes broken in front
of it if it finds in pipeID column and leave empty if it couldn't find. I
think I have to combine Hlookup and if command but I couldn't recognize how.
Will be happy if there is any help.

 

[Pete_UK]

Assume your pipeIDs are in column A from A2 to A1400. Assume your
Target pipes are in column C, beginning with C2. Enter this formula in
D2:

=IF(ISNA(VLOOKUP(C2,A$2:A$1400,1,0)),"","Broken "&C2)

Copy the formula down for as many items as you have in column C.

Hope this helps.

Pete

 

[Khoshravan]

Thanks a lot. That was a wonderfull solution. You saved me a lot of time. How
should I thank you?

 

[Khoshravan]

Dear Pete
Thank you very much for nice solution which moved me completely.
In the next step, I have to do this task for 50 earthquke scenarios, which
means in each earthquake scenario, around 100 pipes broken (Targer Pipes)
(number of broken pipes is not fixed in each scenario and differes from case
to case. For case one it was 78). Info of broken pipes for each scenario is
stored in one sheet. So I have 50 sheets, named run1 to run 50.
Maybe the best way is to write a MAcro.
I am not at wroting macro so I am looking for another easier way.
My problem is how to change run"i" sheet number when copying the formula you
mentioned, as it look to different sheets in each scenario (run1, run2,...
run50).
Woul be happy to get your opinion.
regards
rasoul

 

[Pete_UK]

I would suggest that you have your reference data (1400 pipes) in a
sheet called "data", and that you have the target pipe data in each of
your "run" sheets. That way your formula would always reference back to
one sheet, as follows:

=IF(ISNA(VLOOKUP(C2,'data'!A$2:A$1400,1,0)),"","Broken "&C2)

You could set up "run_x" sheet with this formula in D2 and use this as
a template to produce run1, run2, run3 sheets etc, just by CTRL-drag.
Then, when you add the target pipes to C2 downwards in each of the
"run" sheets, all you need to do is to select cell D2 and double-click
the fill-handle to copy the formula down for as many pipes as you have
in that run. (The fill-handle is the small black square in the bottom
right corner of the cursor).

Obviously, you may need to adjust references to C and D to suit your
sheet layout.

Hope this helps.

Pete