ISNA

[Lomax]

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

 

[Bob Phillips]

=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]

Thanks Bob,

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

Have a great day.

Lomax

 

[Bob Phillips]

Pleasure Lomax.

Thanks Bob,

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

Have a great day.

Lomax

 

[Aladin Akyurek]

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,VLOOKUP(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.

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.