using vlookup, but error #N/A

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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:
 
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:
 
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
 
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.
 
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
 
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.
 
Back
Top