Array formula and #N/A

[Graham Haughs]

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

 

[Max]

With an error trap to return "Free" instead of #N/As,
try array-entered:
=IF(ISNA(MATCH(1,(F2=Bookings!C2:C10000)*($C$5=Bookings!B2:B10000),0)),"Free
",INDEX(Bookings!A2:A10000,MATCH(1,(F2=Bookings!C2:C10000)*($C$5=Bookings!B2
:B10000),0)))

 

[Aladin Akyurek]

Graham,

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

{=LOOKUP(REPT("z",255),CHOOSE({1,2},"Free",INDEX(Bookings!$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]

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]

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

---

 

[Graham Haughs]

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]

You're welcome, Graham !
Thanks for feedback ..