Numbers Lookup Finding incorrect values I have many tables with two columns (key-value style) and I want to combine all the values in a single table. I lookup key in a first column and insert a value from the second one. I've encountered an issue when a wrong key is looked up. Here is a simple example:



The RBYourselfNotUsedRule is not present in Table 1 and 271 belongs to RBVariableReferencedOnceRule. Does anybody have an idea why RBVariableReferencedOnceRule was looked up for RBYourselfNotUsedRule?
If the LOOKUP function can't find the search-for value, the function matches the largest value in search-where that is less than or equal to search-for.  This is why, when the formula cannot find the match, it returns the closest thing it can find.  

From Apple's Documentation:


  LOOKUP(search-for, search-where, result-values) search-for: The value
  to find. search-value can contain any value.
  
  search-where: The collection containing the values to be searched.
  search-where can contain any values.
  
  result-values: An optional collection containing the value to be
  returned based on the search. result-values can contain any values.
  
  Notes
  VLOOKUP compares a search value to the values in the leftmost
  column of a specified collection. Unless an exact match is required,
  the row containing the largest left-column value that is less than or
  equal to the search value is selected. Then, the value from the
  specified column in that row is returned by the function. If an exact
  match is required and none of the leftmost-column values match the
  search value, the function returns an error.


When opting for the exact match, you could wrap the formula in a IFERROR() function to catch the "no match" error and return whatever you specify.  i.e.: "" empty string or other message. "No Match" as a example.

You may need to use the more powerful function VLOOKUP().

Example:



A more powerful combination of functions to perform lookups is INDEX(MATCH()). [This function gives much more power for looking up references without having to know the exact column number.]  For more information and an example, see Using INDEX MATCH for Advanced Lookups | Deskbright.  Even though this reference is for Excel, Numbers supports these functions just as well.