1. That's how SEARCH handles empty cells. You could use an IF test to see if
the cell is empty:

=IF(C4="","",SUMPRODUCT(--(ISNUMBER(SEARCH(C4............

If the cell is empty the formula returns a blank.

2. As long as the colon is a standard character in all the project names in
book2 you can use a formula like this to "flag" the projects:

SubProject 1
MainProject:SubProject 1

=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t
Found")

--
Biff
Microsoft Excel MVP


"grateful" wrote in message
...
Hi, That worked beautifully! Thank you so much. This is what worked for
me.
=SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146)

I have two questions now....I don't know if you can help me.

1) Not all the rows in the first work book have projects...so the formula
is
actually returning the number "8752". Do you know why it's doing that?

2) Also is there a formula I could use in Work Book 2, to highlight all
the
projects that are not found in workbook 1?

Thank you so much!



"T. Valko" wrote:

One way:

=SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400)

Have the other file open.
Start typing the formula:

=SUMPRODUCT(--(ISNUMBER(SEARCH(C1,

When you get that far use your mouse to point to the range(s) in the
other
file.

--
Biff
Microsoft Excel MVP


"grateful" wrote in message
...
Hi there.

I have two workbooks.

In the first workbook column C has about 200 subproject names.
(Ex. C
SubProject 1
SubProject 2
SubProject 3)



In the second workbook column B there are about 400 projects
(Ex. B C
MainProject:SubProject 1 10
Mainproject:SubProject 3 5
Mainproject:SubProject 2 20)


Those 200 projects are somewhere in the 400 projects listed in the 2nd
workbook.

I need a formula in Workbook 1 Column D to look for the sameproject in
Workbook 2, column B, even though column B in workbook 2 contains more
text
than Column C in workbook 1...and then put the corresponding value into
Column D Worksheet 1 in the row with the matching project.

My difficulty is making an Index Formula that uses Match...when the two
cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a
True
or False...But I'm pretty sure I then need to INDEX...

Can anyone help?? :o)

Thank you so much!

You are all so smart.