Numbers Lookup Finding incorrect values

numbers

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:

enter image description here

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?

Best Answer

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:

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.

Related Question