I have a column of text that will have a column of numbers associated with
them.
I need to have the number appear on different spreadsheets when the text
appears appears next on that spreadsheet.

Text will appear on Project List sheet and may have a number next to it. An
IF function populates the other spreadsheets with the text from the Project
List sheet.

I need a formula that will:
Return "" if there is no matching text on the Project List sheet, the text
reference cell on Project List is blank or the number column on Project List
is blank.

This seems to be working, but I hope there is a more efficient way to do it:

=IF(ISERROR(IF(OR(ISNA(MATCH(A6,ProjectName,0)),IS BLANK(MATCH(A6,ProjectName,0)),IF(ISNA(VLOOKUP(A6, ProjectName2,2,0)),"",VLOOKUP(A6,ProjectName2,2,0) =""),ISNA(VLOOKUP(A6,ProjectName2,2,0))),"",VLOOKU P(A6,ProjectName2,2,0))),"",IF(OR(ISNA(MATCH(A6,Pr ojectName,0)),ISBLANK(MATCH(A6,ProjectName,0)),IF( ISNA(VLOOKUP(A6,ProjectName2,2,0)),"",VLOOKUP(A6,P rojectName2,2,0)=""),ISNA(VLOOKUP(A6,ProjectName2, 2,0))),"",VLOOKUP(A6,ProjectName2,2,0)))

In some cases I was #Value and I couldn't figure out what was causing it, so
I wrapped the whole thing in a IF function and slapped it with ISERROR.

Any thoughts about how to tighted this up using Excel?
--
tj