software

The array formula below does everything I want it to do but with one
annoying exception. When the value searched is not there it returns #N/A.
In my ideal world I would like it to return the word quot;Freequot; when the
value is not present. I would settle for blank but the above would be
perfect. Is there anyway within this array formula to accomodate this or
do I need to turn to code? Again I value any guidance or help.{=INDEX(Bookings!A2:A10000,MATCH(1,(F2=Bookings!C2 :C10000)*($C$5=Bookings!B2:B10000),0))}

Graham Haughs
Turriff
Scotland

With an error trap to return quot;Freequot; instead of #N/As,
try array-entered:
=IF(ISNA(MATCH(1,(F2=Bookings!C2:C10000)*($C$5=Boo kings!B2:B10000),0)),quot;Free
quot;,INDEX(Bookings!A2:A10000,MATCH(1,(F2=Bookings!C2 :C10000)*($C$5=Bookings!B2
:B10000),0)))
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Graham Haughsquot; gt; wrote in message
...
gt; The array formula below does everything I want it to do but with one
gt; annoying exception. When the value searched is not there it returns #N/A.
gt; In my ideal world I would like it to return the word quot;Freequot; when the
gt; value is not present. I would settle for blank but the above would be
gt; perfect. Is there anyway within this array formula to accomodate this or
gt; do I need to turn to code? Again I value any guidance or help.
gt;
gt;
gt;
{=INDEX(Bookings!A2:A10000,MATCH(1,(F2=Bookings!C2 :C10000)*($C$5=Bookings!B2
:B10000),0))}
gt;
gt; Graham Haughs
gt; Turriff
gt; Scotland
Graham,

If Bookings!A2:A10000 consists of text values, try:

{=LOOKUP(REPT(quot;zquot;,255),CHOOSE({1,2},quot;Freequot;,INDEX(B ookings!$A$2:$A$10000,MATCH(1,(F2=Bookings!$C$2:$C $10000)*($C$5=Bookings!$B$2:$B$10000),0))))}

If you copy this formula to a large number of cells, the performance can
be comprimised.

Graham Haughs wrote:
gt; The array formula below does everything I want it to do but with one
gt; annoying exception. When the value searched is not there it returns #N/A.
gt; In my ideal world I would like it to return the word quot;Freequot; when the
gt; value is not present. I would settle for blank but the above would be
gt; perfect. Is there anyway within this array formula to accomodate this or
gt; do I need to turn to code? Again I value any guidance or help.
gt;
gt;
gt; {=INDEX(Bookings!A2:A10000,MATCH(1,(F2=Bookings!C2 :C10000)*($C$5=Bookings!B2:B10000),0))}
gt;
gt;
gt; Graham Haughs
gt; Turriff
gt; Scotland

That works for me Max. Strangely I get a small empty square character at
the end of the Free, but this is not important. Many thanks.

Graham

Max wrote:
gt; With an error trap to return quot;Freequot; instead of #N/As,
gt; try array-entered:
gt; =IF(ISNA(MATCH(1,(F2=Bookings!C2:C10000)*($C$5=Boo kings!B2:B10000),0)),quot;Free
gt; quot;,INDEX(Bookings!A2:A10000,MATCH(1,(F2=Bookings!C2 :C10000)*($C$5=Bookings!B2
gt; :B10000),0)))
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Graham Haughsquot; gt; wrote in message
gt; ...
gt;
gt;gt;The array formula below does everything I want it to do but with one
gt;gt;annoying exception. When the value searched is not there it returns #N/A.
gt;gt;In my ideal world I would like it to return the word quot;Freequot; when the
gt;gt;value is not present. I would settle for blank but the above would be
gt;gt;perfect. Is there anyway within this array formula to accomodate this or
gt;gt;do I need to turn to code? Again I value any guidance or help.
gt;gt;
gt;gt;
gt;gt;
gt;
gt; {=INDEX(Bookings!A2:A10000,MATCH(1,(F2=Bookings!C2 :C10000)*($C$5=Bookings!B2
gt; :B10000),0))}
gt;
gt;gt;Graham Haughs
gt;gt;Turriff
gt;gt;Scotland
gt;
gt;
gt;

quot;Graham Haughsquot; gt; wrote in message
...
gt; That works for me Max. Strangely I get a small empty square character at
gt; the end of the Free, but this is not important. Many thanks.

That's just a stray line break character lt;ggt;, introduced unknowingly when
you copy gt; pasted the formula direct from the post into the cell/formula
bar. Just dress it up by deleting the stray square character in the cell
before array-entering. The phrase should appear in the formula as just:
quot;Freequot;.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
Sorry Max, been away so missed your reply on carriage return... Oooops
looking for a hole to hide in! Many thanks again for checking it out.

Graham

Max wrote:
gt; quot;Graham Haughsquot; gt; wrote in message
gt; ...
gt;
gt;gt;That works for me Max. Strangely I get a small empty square character at
gt;gt;the end of the Free, but this is not important. Many thanks.
gt;
gt;
gt; That's just a stray line break character lt;ggt;, introduced unknowingly when
gt; you copy gt; pasted the formula direct from the post into the cell/formula
gt; bar. Just dress it up by deleting the stray square character in the cell
gt; before array-entering. The phrase should appear in the formula as just:
gt; quot;Freequot;.
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt;
gt;

You're welcome, Graham !
Thanks for feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Graham Haughsquot; gt; wrote in message
...
gt; Sorry Max, been away so missed your reply on carriage return... Oooops
gt; looking for a hole to hide in! Many thanks again for checking it out.
gt;
gt; Graham