I am attempting to search a second worksheet for and exact match using a
parameter in the first worksheet. I've solve the NA problem thanks to this
forum and MOST of the searches are turning up good data.

Unfortunately, I have a master list of hospitals in spreadsheet A and am
trying to mark which ones are transplant facilities from a list of these
hospitals off of spreadsheet B.

The formula finds the matches just fine but if a particular row is not found
in the subset list on B, it puts down the previous match until it finds the
next match and so on... I want anything that does not match to give a 0 or
blank (or similar). I would appreciate it if someone can help.

--
Doug S

assuming you are using a VLOOKUP()

you should use ,FALSE at the end of it to only view exact matches:

=VLOOKUP(A2,Sheet2!A:B,2,FALSE)

Since some may not have exact matches, us ISNA to account for those..

=IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE)),"",VLOOKU P(A2,Sheet2!A:B,2,FALSE)
)

Would leave the formula cell blank if there is no exact match.


"Doug S" wrote:

I am attempting to search a second worksheet for and exact match using a
parameter in the first worksheet. I've solve the NA problem thanks to this
forum and MOST of the searches are turning up good data.

Unfortunately, I have a master list of hospitals in spreadsheet A and am
trying to mark which ones are transplant facilities from a list of these
hospitals off of spreadsheet B.

The formula finds the matches just fine but if a particular row is not found
in the subset list on B, it puts down the previous match until it finds the
next match and so on... I want anything that does not match to give a 0 or
blank (or similar). I would appreciate it if someone can help.

--
Doug S

Thanks for responding so quickly... Actually, I'm using LOOKUP() since I
can't gaurantee the data will be sorted by the client. I am pasting the
actual formula here.

=IF(ISNA(LOOKUP(E14,Transplant_Centers!$A$2:$A$100 ,Transplant_Centers!$B$2:$B$100)),"",LOOKUP(E14,Tr ansplant_Centers!$A$2:$A$100,Transplant_Centers!$B $2:$B$100))

Information in the E14 and column A is actually the ID key field on both
worksheets.


--
Doug S


"Sean Timmons" wrote:

assuming you are using a VLOOKUP()

you should use ,FALSE at the end of it to only view exact matches:

=VLOOKUP(A2,Sheet2!A:B,2,FALSE)

Since some may not have exact matches, us ISNA to account for those..

=IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE)),"",VLOOKU P(A2,Sheet2!A:B,2,FALSE)
)

Would leave the formula cell blank if there is no exact match.


"Doug S" wrote:

I am attempting to search a second worksheet for and exact match using a
parameter in the first worksheet. I've solve the NA problem thanks to this
forum and MOST of the searches are turning up good data.

Unfortunately, I have a master list of hospitals in spreadsheet A and am
trying to mark which ones are transplant facilities from a list of these
hospitals off of spreadsheet B.

The formula finds the matches just fine but if a particular row is not found
in the subset list on B, it puts down the previous match until it finds the
next match and so on... I want anything that does not match to give a 0 or
blank (or similar). I would appreciate it if someone can help.

--
Doug S

You can't use LOOKUP in this application. By default, using LOOKUP, if an
exact match isn't found then it will return a "closest" match that is less
than the lookup_value. For example:

A...x
C...o
D...t

If you lookup "B" it doesn't exist and the closest match that is less than
"B" will be "A" so the result you'll get is x.

If the values being returned are hospital names then I'm assuming these are
TEXT values.

Try this:

=LOOKUP("zzzzzz",CHOOSE({1,2},"",VLOOKUP(E14,Trans plant_Centers!$A$2:$B$100,2,0)))Ok, so you might be wondering how I can say: "You can't use LOOKUP in thisapplication", yet I'm using LOOKUP!Well, in this application LOOKUP is not being used to find the hospitalname. LOOKUP is being used to trap any errors.Here's how it works...The result of VLOOKUP will be either a hospital name (a TEXT string) or anerror (when an exact match isn't found). We use the outer LOOKUP todetermine whether the grand result of the formula should be a blank or ahospital name.When the VLOOKUP returns a hospital name this is what we get:=LOOKUP("zzzzzz",{"";"UPMC"})Since there is not an exact match of "zzzzzz" LOOKUP returns the last valuethat is less than "zzzzzz". That value is the hospital name UPMC (Universityof Pittsburgh Medical Center).When the VLOOKUP returns an error because an exact match wan't found this iswhat we get:=LOOKUP("zzzzzz",{"";#N/A})Since there is not an exact match of "zzzzzz" *and* because LOOKUP ignoreserrors, LOOKUP returns the last value that is less than "zzzzzz". That valueis the blank "".exp101--BiffMicrosoft Excel MVP"Doug S" wrote in ... Thanks for responding so quickly... Actually, I'm using LOOKUP() since I can't gaurantee the data will be sorted by the client. I am pasting the actual formula here.=IF(ISNA(LOOKUP(E14,Transplant_Centers!$A$2 :$A$100,Transplant_Centers!$B$2:$B$100)),"",LOOKUP (E14,Transplant_Centers!$A$2:$A$100,Transplant_Cen ters!$B$2:$B$100)) Information in the E14 and column A is actually the ID key field on both worksheets. -- Doug S "Sean Timmons" wrote: assuming you are using a VLOOKUP() you should use ,FALSE at the end of it to only view exact matches: =VLOOKUP(A2,Sheet2!A:B,2,FALSE) Since some may not have exact matches, us ISNA to account for those..=IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE) ),"",VLOOKUP(A2,Sheet2!A:B,2,FALSE) ) Would leave the formula cell blank if there is no exact match. "Doug S" wrote: I am attempting to search a second worksheet for and exact match usinga parameter in the first worksheet. I've solve the NA problem thanks tothis forum and MOST of the searches are turning up good data. Unfortunately, I have a master list of hospitals in spreadsheet A andam trying to mark which ones are transplant facilities from a list ofthese hospitals off of spreadsheet B. The formula finds the matches just fine but if a particular row is notfound in the subset list on B, it puts down the previous match until it findsthe next match and so on... I want anything that does not match to give a 0or blank (or similar). I would appreciate it if someone can help. -- Doug S

Oh, that's just lovely! <sigh

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
You can't use LOOKUP in this application. By default, using LOOKUP, if an
exact match isn't found then it will return a "closest" match that is less
than the lookup_value. For example:

A...x
C...o
D...t

If you lookup "B" it doesn't exist and the closest match that is less than
"B" will be "A" so the result you'll get is x.

If the values being returned are hospital names then I'm assuming these
are TEXT values.

Try this:

=LOOKUP("zzzzzz",CHOOSE({1,2},"",VLOOKUP(E14,Trans plant_Centers!$A$2:$B$100,2,0)))Ok,
so you might be wondering how I can say: "You can't use LOOKUP in
thisapplication", yet I'm using LOOKUP!Well, in this application LOOKUP is
not being used to find the hospitalname. LOOKUP is being used to trap any
errors.Here's how it works...The result of VLOOKUP will be either a
hospital name (a TEXT string) or anerror (when an exact match isn't
found). We use the outer LOOKUP todetermine whether the grand result of
the formula should be a blank or ahospital name.When the VLOOKUP returns a
hospital name this is what we get:=LOOKUP("zzzzzz",{"";"UPMC"})Since there
is not an exact match of "zzzzzz" LOOKUP returns the last valuethat is
less than "zzzzzz". That value is the hospital name UPMC (Universityof
Pittsburgh Medical Center).When the VLOOKUP returns an error because an
exact match wan't found this iswhat we
get:=LOOKUP("zzzzzz",{"";#N/A})Since there is not an exact match of
"zzzzzz" *and* because LOOKUP ignoreserrors, LOOKUP returns the last
value that is less than "zzzzzz". That valueis the blank
"".exp101--BiffMicrosoft Excel MVP"Doug S"
wrote in
... Thanks
for responding so quickly... Actually, I'm using LOOKUP() since I can't
gaurantee the data will be sorted by the client. I am pasting the actual
formula
here.=IF(ISNA(LOOKUP(E14,Transplant_Centers!$A$2 :$A$100,Transplant_Centers!$B$2:$B$100)),"",LOOKUP (E14,Transplant_Centers!$A$2:$A$100,Transplant_Cen ters!$B$2:$B$100))
Information in the E14 and column A is actually the ID key field on both
worksheets. -- Doug S "Sean Timmons" wrote: assuming you are
using a VLOOKUP() you should use ,FALSE at the end of it to only view
exact matches: =VLOOKUP(A2,Sheet2!A:B,2,FALSE) Since some may not
have exact matches, us ISNA to account for
those..=IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE) ),"",VLOOKUP(A2,Sheet2!A:B,2,FALSE)
) Would leave the formula cell blank if there is no exact
match. "Doug S" wrote: I am attempting to search a second
worksheet for and exact match usinga parameter in the first worksheet.
I've solve the NA problem thanks tothis forum and MOST of the searches
are turning up good data. Unfortunately, I have a master list of
hospitals in spreadsheet A andam trying to mark which ones are
transplant facilities from a list ofthese hospitals off of spreadsheet
B. The formula finds the matches just fine but if a particular row
is notfound in the subset list on B, it puts down the previous match
until it findsthe next match and so on... I want anything that does
not match to give a 0or blank (or similar). I would appreciate it if
someone can help. -- Doug S

Worked like a charm... Thanks!!!


--
Doug S


"T. Valko" wrote:

Oh, that's just lovely! <sigh

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
You can't use LOOKUP in this application. By default, using LOOKUP, if an
exact match isn't found then it will return a "closest" match that is less
than the lookup_value. For example:

A...x
C...o
D...t

If you lookup "B" it doesn't exist and the closest match that is less than
"B" will be "A" so the result you'll get is x.

If the values being returned are hospital names then I'm assuming these
are TEXT values.

Try this:

=LOOKUP("zzzzzz",CHOOSE({1,2},"",VLOOKUP(E14,Trans plant_Centers!$A$2:$B$100,2,0)))Ok,
so you might be wondering how I can say: "You can't use LOOKUP in
thisapplication", yet I'm using LOOKUP!Well, in this application LOOKUP is
not being used to find the hospitalname. LOOKUP is being used to trap any
errors.Here's how it works...The result of VLOOKUP will be either a
hospital name (a TEXT string) or anerror (when an exact match isn't
found). We use the outer LOOKUP todetermine whether the grand result of
the formula should be a blank or ahospital name.When the VLOOKUP returns a
hospital name this is what we get:=LOOKUP("zzzzzz",{"";"UPMC"})Since there
is not an exact match of "zzzzzz" LOOKUP returns the last valuethat is
less than "zzzzzz". That value is the hospital name UPMC (Universityof
Pittsburgh Medical Center).When the VLOOKUP returns an error because an
exact match wan't found this iswhat we
get:=LOOKUP("zzzzzz",{"";#N/A})Since there is not an exact match of
"zzzzzz" *and* because LOOKUP ignoreserrors, LOOKUP returns the last
value that is less than "zzzzzz". That valueis the blank
"".exp101--BiffMicrosoft Excel MVP"Doug S"
wrote in
... Thanks
for responding so quickly... Actually, I'm using LOOKUP() since I can't
gaurantee the data will be sorted by the client. I am pasting the actual
formula
here.=IF(ISNA(LOOKUP(E14,Transplant_Centers!$A$2 :$A$100,Transplant_Centers!$B$2:$B$100)),"",LOOKUP (E14,Transplant_Centers!$A$2:$A$100,Transplant_Cen ters!$B$2:$B$100))
Information in the E14 and column A is actually the ID key field on both
worksheets. -- Doug S "Sean Timmons" wrote: assuming you are
using a VLOOKUP() you should use ,FALSE at the end of it to only view
exact matches: =VLOOKUP(A2,Sheet2!A:B,2,FALSE) Since some may not
have exact matches, us ISNA to account for
those..=IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE) ),"",VLOOKUP(A2,Sheet2!A:B,2,FALSE)
) Would leave the formula cell blank if there is no exact
match. "Doug S" wrote: I am attempting to search a second
worksheet for and exact match usinga parameter in the first worksheet.
I've solve the NA problem thanks tothis forum and MOST of the searches
are turning up good data. Unfortunately, I have a master list of
hospitals in spreadsheet A andam trying to mark which ones are
transplant facilities from a list ofthese hospitals off of spreadsheet
B. The formula finds the matches just fine but if a particular row
is notfound in the subset list on B, it puts down the previous match
until it findsthe next match and so on... I want anything that does
not match to give a 0or blank (or similar). I would appreciate it if
someone can help. -- Doug S



.