ISNA
 

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)))

=IF(ISNA(VLOOKUP(D594,'OHME IT'!$E$2:$P$2759,12,FALSE)),"No data
available",VLOOKUP(D594,'OHME IT'!$E$2:$P$2759,12,FALSE))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lomax" wrote in message
news:TxNEe.47887$up5.19939@lakeread02...
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)))

Thanks Bob,

Really appreciate you, this Group and all that are so helpful..

Have a great day.

Lomax


"Bob Phillips" wrote in message
...
=IF(ISNA(VLOOKUP(D594,'OHME IT'!$E$2:$P$2759,12,FALSE)),"No data
available",VLOOKUP(D594,'OHME IT'!$E$2:$P$2759,12,FALSE))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lomax" wrote in message
news:TxNEe.47887$up5.19939@lakeread02...
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)))

Pleasure Lomax.


"Lomax" wrote in message
news:NSNEe.47888$up5.20844@lakeread02...
Thanks Bob,

Really appreciate you, this Group and all that are so helpful..

Have a great day.

Lomax


"Bob Phillips" wrote in message
...
=IF(ISNA(VLOOKUP(D594,'OHME IT'!$E$2:$P$2759,12,FALSE)),"No data
available",VLOOKUP(D594,'OHME IT'!$E$2:$P$2759,12,FALSE))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lomax" wrote in message
news:TxNEe.47887$up5.19939@lakeread02...
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)))

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.