using vlookup, but error #N/A

E

ErinGertz

Hi

I'm trying to type in a social security number and then write a vlookup formula for the next cell so it pulls up the patient's last name. The worksheet I need to pull the info from is called PATIENTS. That sheet is set up so that the first column has the social, then 2nd column is another identifying #, then the 3rd column is the patient's last name. The first box that I type in the social security number in the current worksheet is A5. This is the formula I've written:

=IF(ISNA(MATCH(A5,PATIENTS!$A$2:$A$2070,FALSE)),VLOOKUP(A5,PATIENTS!$A$2:$M$2070,3,FALSE)

But I keep getting the error message #N/A

Please help!
 
V

Vasant Nanavati

Try:

=IF(ISNA(VLOOKUP(A5,PATIENTS!$A$2:$A$2070,FALSE)),"",VLOOKUP(A5,PATIENTS!$A$
2:$C$2070,3,FALSE))

You can substitute "Record not found" for "".

--

Vasant


ErinGertz said:
Hi,

I'm trying to type in a social security number and then write a vlookup
Click to expand...
formula for the next cell so it pulls up the patient's last name. The
worksheet I need to pull the info from is called PATIENTS. That sheet is
set up so that the first column has the social, then 2nd column is another
identifying #, then the 3rd column is the patient's last name. The first
box that I type in the social security number in the current worksheet is
A5. This is the formula I've written:
 
A

Andy Wiggins

1) Your formula is testing whether the result of the MATCH is #N/A. If that
returns TRUE (ie. the result is #N/A) then VLOOKUP is going to try and look
up something that the formula has established doesn't exist.
2) Your formula doesn't have a FALSE condition.

Try using this version:

IF(ISNA(MATCH(A5,PATIENTS!$A$2:$A$2070,FALSE)),"",VLOOKUP(A5,PATIENTS!$A$2:$
M$2070,3,FALSE))

Notice the additional ,"", which will return a blank if the test returns
TRUE.

--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"


ErinGertz said:
Hi,

I'm trying to type in a social security number and then write a vlookup
Click to expand...
formula for the next cell so it pulls up the patient's last name. The
worksheet I need to pull the info from is called PATIENTS. That sheet is
set up so that the first column has the social, then 2nd column is another
identifying #, then the 3rd column is the patient's last name. The first
box that I type in the social security number in the current worksheet is
A5. This is the formula I've written:
 
E

Erin Gertz

Thanks for the replies.

I tried the new formulas, with the empty quotes, but now I'm not getting
anything.

The PATIENTS worksheet is linked to an Access database through a query.
Could that have anything to do with it?

-Erin



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
R

Ron Cross

Are you sure your range is correct. i.e. the block of data that you want to
match too? Confirm that. Maybe you get n/a because the formula is not
looking at the entire range and thus doesn't pick up the match.
 
E

Erin Gertz

The data I want to match to is the social security number which is
located in the first column of the worksheet PATIENTS.

So I think its correct. What do you think?

-Erin



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
V

Vasant Nanavati

The way you have described it; it is correct. The "" gives a blank only when
there is no match.

Are you sure that one set of SSNs is not formated as number and the other as
text? Text formatting causes unpredictable behavior.
 
Top