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,,,

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,,,


.

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"

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,,,

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

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,,,

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

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"

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,,,