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)),VL OOKUP(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" wrote in message
...
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)),VL OOKUP(A5,PATIENTS!$A$2:$M$
2070,3,FALSE))

But I keep getting the error message #N/A

Please help!

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" wrote in message
...
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)),VL OOKUP(A5,PATIENTS!$A$2:$M$
2070,3,FALSE))

But I keep getting the error message #N/A

Please help!

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!

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.

--
RONALD CROSS
"Erin Gertz" wrote in message
...
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!

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!

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.

--

Vasant


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