Simplify formula

[Guest]

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)),ISBLANK(MATCH(A6,ProjectName,0)),IF(ISNA(VLOOKUP(A6,ProjectName2,2,0)),"",VLOOKUP(A6,ProjectName2,2,0)=""),ISNA(VLOOKUP(A6,ProjectName2,2,0))),"",VLOOKUP(A6,ProjectName2,2,0))),"",IF(OR(ISNA(MATCH(A6,ProjectName,0)),ISBLANK(MATCH(A6,ProjectName,0)),IF(ISNA(VLOOKUP(A6,ProjectName2,2,0)),"",VLOOKUP(A6,ProjectName2,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?

 

[Biff]

I'm not really following your description but....

ISBLANK(MATCH(A6,ProjectName,0))

What are you trying to do with that expression?

That will never evaluate to TRUE.

Biff

 

[Bernard Liengme]

Let's start simple
The named range ProjectList looks like this with text in one column and
numbers in the one to the right.
Apple 3
Banana 6
Cherry
Damson 9

Then this formula on any sheet =VLOOKUP(A6,ProjectList,2,0) should return
the number 3 if A6 on that sheet has the text Apple.

If A6 has the text Cherry you will see 0. To avoid the zero use
either =IF(VLOOKUP(A6,ProjectList,2,0)>0,VLOOKUP(A6,ProjectList,2,0),"")
or =IF(VLOOKUP(A6,ProjectList,2,0)=0," ", VLOOKUP(A6,ProjectList,2,0))

Suppose the text in A6 is Plum which does not appear in the table?Then you
get #N/A. To get rid of this use
=IF(ISNA(VLOOKUP(A6,ProjectList,2,0))," ",IF(VLOOKUP(A6,ProjectList,2,0)=0,"
",VLOOKUP(A6,ProjectList,2,0)))
You cannot use
=IF(OR(ISNA(VLOOKUP(A6,ProjectList,2,0)),VLOOKUP(A6,ProjectList,2,0)=0) ...

best wishes

 

[Guest]

I'll give this a try. Those stupid zeroes started this whole thing and it
mushroomed out of control.

 

[Guest]

Your suggestions work. Thanks.

If I am reading your response correctly, the #NA error must be caught before
testing any other conditions. Is that correct? I am wondering exactly why the
OR can't be used.

 

[Bernard Liengme]

You are correct, you must trap the N/A error