LOOKUP returning unexpected results
I have two tables in a Numbers 09 file; one contains 302 records, the other 230 records.
Each of the individuals with a record in the small table has a record in the larger one; some of the individuals in the large table do not have a record in the smaller one. Each individual has a unique ID.
The goal is to match records between the two tables (based upon IDs) and copy a value from a column in the small table to an empty column in the large table.
I tried to accomplish this using a LOOKUP formula in the empty column in the large table.
=LOOKUP($B2,'small table' :: $B$2:$B$230,'small table' :: $W$2:$W$230)
Column B in each table contains IDs. Column W in the small table contains the values to be copied to the column in the large table containing the f.
In one record in the large table that is unique to that table, the above formula correctly returns an erro—LOOKUP could not find the requested value.
In all other records in the large table that are unique to that table, the above formula returns a value!
I am stumped—how come the formula is returning a value rather than an error for the records in the large table that don't have a match in the small one?
For the records that do match between the two tables, the LOOKUP correctly copies the value from the small table to the large one.
I've formatted the ID field in both tables to Text.
I've checked the values in the ID field in both tables for leading or trailing blanks; there are none.
Thanks in advance for any suggestions/corrections.
iMac, Mac OS X (10.7.5)