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","")
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","")
=if(isna(lookup(A3,!$H14:$H27),"","S")
If it's always S or blank, you don't need to match A3...
*******************
~Anne Troy
www.OfficeArticles.com
"Sympatico News" <[email protected]> wrote in message
news:[email protected]...
> 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","")
>
>
Hi,
Try this:
=IF(ISNA(LOOKUP(A3,!$H14:$H27)),"","S")
Regards,
KL
"Sympatico News" <[email protected]> wrote in message
news:[email protected]...
>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
"Anne Troy" <[email protected]> wrote in message
news:[email protected]...
> =if(isna(lookup(A3,!$H14:$H27),"","S")
>
> If it's always S or blank, you don't need to match A3...
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
>
>
> "Sympatico News" <[email protected]> wrote in message
> news:[email protected]...
>> 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","")
>>
>>
>
>
Hi,
You should probably make the row references absolute too:
=IF(ISNA(LOOKUP(A3,!$H$14:$H$27)),"","S")
Regards,
KL
"Sympatico News" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Anne Troy" <[email protected]> wrote in message
> news:[email protected]...
>> =if(isna(lookup(A3,!$H14:$H27),"","S")
>>
>> If it's always S or blank, you don't need to match A3...
>> *******************
>> ~Anne Troy
>>
>> www.OfficeArticles.com
>>
>>
>> "Sympatico News" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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","")
>>>
>>>
>>
>>
>
>
Solved - need an exact match that only VLOOKUP can give me.
=IF(ISNA(VLOOKUP(C3,'Fiscal Years'!$B13:$B27,1,FALSE)),"","S")
Thankyou both
"KL" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> You should probably make the row references absolute too:
>
> =IF(ISNA(LOOKUP(A3,!$H$14:$H$27)),"","S")
>
> Regards,
> KL
>
>
> "Sympatico News" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>> "Anne Troy" <[email protected]> wrote in message
>> news:[email protected]...
>>> =if(isna(lookup(A3,!$H14:$H27),"","S")
>>>
>>> If it's always S or blank, you don't need to match A3...
>>> *******************
>>> ~Anne Troy
>>>
>>> www.OfficeArticles.com
>>>
>>>
>>> "Sympatico News" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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","")
>>>>
>>>>
>>>
>>>
>>
>>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks