If having it all in a single formula required, here is a different take:

(a) if the VLOOKUP formula you have is expected to return text values:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"No Data
Available",VLOOKUP(D594,'OHME IT'!$E$2:$P$2759,12,0)))

(b) if the VLOOKUP formula you have is expected to return numbers:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,VLOOK UP(D594,'OHME
IT'!$E$2:$P$2759,12,0)))

The formula cell for the latter can then be custom-formatted as:

[=0]"No Data Available";General

What really would be the most attractive solution is:

=VLOOKUP(D594,'OHME IT'!$E$2:$P$2759,12,0,"No Data Available")

as per [2] of what I advertise thru in my signature.

Lomax wrote:
Top of the morning..

I have formula below that gives the "true" when there is no data in the
Table array that is being searched.

Is is possible to have something other than "True" entered into cell D594 --
I would prefer "No data Available". If so could you kindly provide changes
to the below.

Thanks lomax

=(ISNA(VLOOKUP(D594,'OHME IT'!$E$2:$P$2759,12,FALSE)))



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.