Array formula and #N/A
 

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 "Free" 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

Array formula and #N/A
 

With an error trap to return "Free" instead of #N/As,
try array-entered:
=IF(ISNA(MATCH(1,(F2=Bookings!C2:C10000)*($C$5=Boo kings!B2:B10000),0)),"Free
",INDEX(Bookings!A2:A10000,MATCH(1,(F2=Bookings!C2 :C10000)*($C$5=Bookings!B2
:B10000),0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Graham Haughs" wrote in message
...
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 "Free" 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

Array formula and #N/A
 

Graham,

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

{=LOOKUP(REPT("z",255),CHOOSE({1,2},"Free",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:
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 "Free" 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

Array formula and #N/A
 

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:
With an error trap to return "Free" instead of #N/As,
try array-entered:
=IF(ISNA(MATCH(1,(F2=Bookings!C2:C10000)*($C$5=Boo kings!B2:B10000),0)),"Free
",INDEX(Bookings!A2:A10000,MATCH(1,(F2=Bookings!C2 :C10000)*($C$5=Bookings!B2
:B10000),0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Graham Haughs" wrote in message
...

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 "Free" 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

Array formula and #N/A
 

"Graham Haughs" wrote in message
...
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.

That's just a stray line break character <g, introduced unknowingly when
you copy 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:
"Free".
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Array formula and #N/A
 

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:
"Graham Haughs" wrote in message
...

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.


That's just a stray line break character <g, introduced unknowingly when
you copy 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:
"Free".
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Array formula and #N/A
 

You're welcome, Graham !
Thanks for feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Graham Haughs" wrote in message
...
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