You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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



.