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,Transplant_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" <
[email protected]> wrote in messageThanks 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_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)),"",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
