Unwanted result in LOOKUP()

  • Thread starter Thread starter Sympatico News
  • Start date Start date
S

Sympatico News

I want to avoid getting "N/A" as a result when there is no match on the
formula below. What I need is a blank on no match but an "S" when it does.

How do we do this? Thought this would work.....

=IF(LOOKUP(A3,!$H14:$H27)=A3,"S","")
 
very nice - thankyou. I'm on track but there's a problem here though. I want
to copy this across the row. The first time I hit a match "S" is returned as
expected. Problem is that all cells after also return an "S" match or not.

The sheet is looking for Stat holidays in the lookup H14:H27. Blank is
returned for 24, 25 which is correct of course, "S" is returned for the 26
and 27 which is also correct but the 28 to 01 should be a blank. 02-Jan is a
valid "S" of course.

My previous formula did a similar thing except I got #N/A until a match
occurred - then the function did what is was supposed to.


S S S S S S S S
24-Dec-05 25-Dec-05 26-Dec-05 27-Dec-05 28-Dec-05 29-Dec-05 30-Dec-05
31-Dec-05 1-Jan-06 2-Jan-06
S S M T W T F S S M



=if(isna(lookup(A3,$H14:$H27),"","S")


Gerry Walsh
 
Hi,

You should probably make the row references absolute too:

=IF(ISNA(LOOKUP(A3,!$H$14:$H$27)),"","S")

Regards,
KL
 
Solved - need an exact match that only VLOOKUP can give me.

=IF(ISNA(VLOOKUP(C3,'Fiscal Years'!$B13:$B27,1,FALSE)),"","S")

Thankyou both
 
Back
Top