Formula returning #N/A

In Sheet1 I have the following formula in B149:

=IF(VLOOKUP(A149,MyClinics,3,FALSE),"Yes","No")

MyClinics is a named range on my sheet - Reference List

In My Define Ranges Dialog Box I have:
MyClinics with RefersTo: as:
='Reference List'!$A$115:$E$222

What are the other possibilities as to why I'm getting
nothing but #N/A returned?
TIA,,,

[Jason Morin]

If you're not returning a value using VLOOKUP, use
something simpler and potentially faster. Try:

=IF(COUNTIF('Reference List'!
$A$115:$A$222,A149),"Yes","No")

HTH
Jason
Atlanta, GA

>-----Original Message-----
>In Sheet1 I have the following formula in B149:
>
>=IF(VLOOKUP(A149,MyClinics,3,FALSE),"Yes","No")
>
>MyClinics is a named range on my sheet - Reference List
>
>In My Define Ranges Dialog Box I have:
>MyClinics with RefersTo: as:
>='Reference List'!$A$115:$E$222
>
>What are the other possibilities as to why I'm getting
>nothing but #N/A returned?
>TIA,,,
>
>
>.
>

[Aladin Akyurek]

Since you seem just need to check whether A149 is in the relevant range...

=ISNUMBER(MATCH(A149,INDEX(MyClinics,0,1),0))+0

or

=ISNUMBER(MATCH(A149,'Reference List'!$A$115:$A$222,0))+0

Custom format the formula cell as:

[0]"No";[=1]"Yes"

[email protected] wrote:
> In Sheet1 I have the following formula in B149:
>
> =IF(VLOOKUP(A149,MyClinics,3,FALSE),"Yes","No")
>
> MyClinics is a named range on my sheet - Reference List
>
> In My Define Ranges Dialog Box I have:
> MyClinics with RefersTo: as:
> ='Reference List'!$A$115:$E$222
>
> What are the other possibilities as to why I'm getting
> nothing but #N/A returned?
> TIA,,,
>
>

[Jim May]

Sorry, but I failed to mention that my cell reference A149 contains PPV
(text)
and MyClinics Column 3 also contains (among others) PPV (text).


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Since you seem just need to check whether A149 is in the relevant range...
>
> =ISNUMBER(MATCH(A149,INDEX(MyClinics,0,1),0))+0
>
> or
>
> =ISNUMBER(MATCH(A149,'Reference List'!$A$115:$A$222,0))+0
>
> Custom format the formula cell as:
>
> [0]"No";[=1]"Yes"
>
> [email protected] wrote:
> > In Sheet1 I have the following formula in B149:
> >
> > =IF(VLOOKUP(A149,MyClinics,3,FALSE),"Yes","No")
> >
> > MyClinics is a named range on my sheet - Reference List
> >
> > In My Define Ranges Dialog Box I have:
> > MyClinics with RefersTo: as:
> > ='Reference List'!$A$115:$E$222
> >
> > What are the other possibilities as to why I'm getting
> > nothing but #N/A returned?
> > TIA,,,
> >
> >

[Aladin Akyurek]

Are you saying that you're checking for the presence of A149 in the 3rd
column of MyClinics? If so, using the same logic, we get:

=ISNUMBER(MATCH(A149,INDEX(MyClinics,0,3),0))+0

=ISNUMBER(MATCH(A149,'Reference List'!$C$115:$C$222,0))+0

Again, apply the Yes/No formatting.

Maybe you're up to something entirely different...

Jim May wrote:
> Sorry, but I failed to mention that my cell reference A149 contains PPV
> (text)
> and MyClinics Column 3 also contains (among others) PPV (text).
>
>
> "Aladin Akyurek" <[email protected]> wrote in message
> news:[email protected]...
>
>>Since you seem just need to check whether A149 is in the relevant range...
>>
>>=ISNUMBER(MATCH(A149,INDEX(MyClinics,0,1),0))+0
>>
>>or
>>
>>=ISNUMBER(MATCH(A149,'Reference List'!$A$115:$A$222,0))+0
>>
>>Custom format the formula cell as:
>>
>>[0]"No";[=1]"Yes"
>>
>>[email protected] wrote:
>>
>>>In Sheet1 I have the following formula in B149:
>>>
>>>=IF(VLOOKUP(A149,MyClinics,3,FALSE),"Yes","No")
>>>
>>>MyClinics is a named range on my sheet - Reference List
>>>
>>>In My Define Ranges Dialog Box I have:
>>>MyClinics with RefersTo: as:
>>>='Reference List'!$A$115:$E$222
>>>
>>>What are the other possibilities as to why I'm getting
>>>nothing but #N/A returned?
>>>TIA,,,
>>>
>>>
>
>
>