LOOKUP returning unexpected results

Question

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)

Posted on Dec 13, 2012 9:30 AM

Reply

Answer 1

Jeff Shenk

Level 4

2,993 points

Dec 13, 2012 10:21 AM in response to Barry Fass-Holmes

LOOKUP only does "close match"; it doesn't have an option for "exact match." So, if there is no exact match, it will return the closest value that doesn't go over the search term.

I think VLOOKUP, which does allow exact match, should work for you.

Reply

Answer 2

Dec 13, 2012 10:46 AM in response to Barry Fass-Holmes

Barry,

Here's an expression that I find useful for lookups, and a screenshot of an example:

=INDEX(Little, MATCH(A, Little :: A), 2)

I left the errors untrapped so you could see the direct result of the lack of a match. In this example the expression in column B of the table Big is searching column A the table Little and returning the content of column B of the table Little where there is a match.

Jerry

Reply

Answer 3

Dec 13, 2012 10:45 AM in response to Jeff Shenk

Hi Jeff,

Thanks for your reply.

Here's what the user guide (p. 204) says about LOOKUP.

The LOOKUP function finds a match for a given search value in one range, then returns the value in the cell with the same relative position in a second range.

Here's what is says (p.210) about VLOOKUP.

The VLOOKUP function returns a value from a range of columns by using the left column of values to pick a row and a column number to pick a column in that row.

…

close-match: An optional value that determines whether an exact match is required.

Before posting this discussion, I tried using VLOOKUP. It returned an error in every record in the large table (even for the records that did have matching IDs)—the formula contains an invalid reference.

Based upon the user guide's instruction to set the optional value to FALSE (0) for exact match, I used the following.

=VLOOKUP($B2,'small table' :: $B$2:$B$230,'small table' :: $W$2:$W$230, FALSE)

This returned the above error for all records.

Any other suggestions?

Reply

Answer 4

Dec 13, 2012 10:49 AM in response to Barry Fass-Holmes

Barry,

An invalid reference error arises when you refer to a cell not in existance. It could ba an incorrect table name or a table with fewer rows than you are referencing, etc.

Jerry

Reply

Answer 5

jaxjason

Level 5

5,035 points

Dec 13, 2012 10:57 AM in response to Barry Fass-Holmes

VLOOKUP($B2,'small table' :: $B$2:$B$230,'small table' :: $W$2:$W$230, FALSE)

your using vlookup wrong here 🙂 (see page 233 of this PDF, the formula and function guide for iwork 09)

Vlookup (what, where, what column to return, false)

So it would be something like vlookup($B2,'small table'::$B$2:$B$230,1,false) which would bring back the ID number in B IF theres a match.

To take care of the non-matches incompass it in a Iferror like so

IFERROR(vlookup($B2,'small table'::$B$2:$B$230,1,false),"NO Match")

This will return the value from vlookup, if the item is found, and "No match" if Vlookup returned an error because it couldnt find a match (#N/A).

So if you want to bring back items in column W, you would do this:

IFERROR(vlookup($B2,'small table'::$B$2:$W$230,22,false),"NO Match")

Since W is the 22nd column when B is the first.

Jason

Reply

Answer 6

Dec 13, 2012 11:00 AM in response to Jerrold Green1

Hi Jerry,

Thanks for taking time to reply.

I must be missing something in your expression.

1. What does the "2" signify?

2. What part of the expression copies the value from Little to Big?

3. What have I done improperly in the following translation of your expression? It includes the option to use an exact match. When I fill the empty column in the large table with the following formula, the results are 1, 2, 3, 4 and so on.

=INDEX(MATCH($B2, 'small table' :: $B$2:$B$230),0)

Reply

Answer 7

Dec 13, 2012 11:01 AM in response to Jerrold Green1

Yes, the small table has fewer records than the large one.

That error happens with VLOOKUP (Jeff's recommendation), not with LOOKUP (what I was using originally).

Reply

Answer 8

Dec 13, 2012 11:17 AM in response to jaxjason

Hi Jason,

Thanks for your reply.

Yes, I did look up (so to speak) VLOOKUP in the formula and function guide before launching this discussion (as quoted in my reply to Jeff above).

I must be missing something in your formula using IFERROR and VLOOKUP.

IFERROR(vlookup($B2,'small table'::$B$2:$W$230,22,false),"NO Match")

Why compare the ID column in the large table (B) against all columns in the small table ($B$2:$W$230)? Column B contains ID; column W contains the values to be copied from the small table to the large one for records that have matching ID.

What does "22" signify or refer to?

Reply

Answer 9

Dec 13, 2012 11:11 AM in response to Barry Fass-Holmes

Here's a rundown on my expression:

=INDEX(Little, MATCH(A, Little :: A), 2)

INDEX syntax:

INDEX(range, row-index, column-index, area-index)

In my expression, "Little" is the name of the range that INDEX will act on.

In my expression, "MATCH(A, Little :: A)" computes the Row Index.

In my expression, the column index is 2 (Column B)

In narrative form:

Return the content of the table Little, where the row address is the row number returned by the MATCH function and the column address is 2.

MATCH searches for the value of column A in the local table, current row. The search area is column A of the table "Little". The result of MATCH is the row number where the match is found.

Jerry

Reply

Answer 10

Jeff Shenk

Level 4

2,993 points

Dec 13, 2012 11:34 AM in response to Barry Fass-Holmes

Yes, I realize the documentation neglects to mention that LOOKUP uses "close match" but nevertheless that is the case.

Jason has explained the correct syntax for VLOOKUP, but quickly, again:

The first parameter is the term you are searching for, which you are using correctly

The second is a range of cells, with the first column being the ones you are searching, but also including the column from which you want to return a value.

The third parameter is an integer, the index of the column from which you want to return the value, relative to the first column in the search range

The fourth parameter is FALSE or 0 if you want an exact match

Reply

Answer 11

Dec 13, 2012 12:00 PM in response to Jerrold Green1

Hi Jerry,

Thanks again for your reply.

Your expression returned the exact same results as my LOOKUP expression which led me to launch this discussion!

In other words, the expression returned a value for records in the large table that did not have a matching ID in the small table.

Here is my version of your expression.

=INDEX('small table', MATCH(ID, 'small table' :: ID), 23)

23 is the column number in the small table that contains the values to be copied from the small table to the empty column in the large table.

Any explanation how come this is happening with both your expression and my LOOKUP one at the beginning of this discussion?

Reply

Answer 12

Dec 13, 2012 12:03 PM in response to Barry Fass-Holmes

Barry (Fass-Holmes),

Can you please post a screenshot of your data? It seem there must be something else going on that the forum participants don't know or can't see.

Reply

Answer 13

Dec 13, 2012 12:10 PM in response to Barry Fass-Holmes

Like Wayne, I can't imagine how the expression could be working in my small example and not in your larger sheet. Could you email me a copy?

Jerry

Reply

Answer 14

Dec 13, 2012 12:14 PM in response to Jeff Shenk

Hi Jeff,

Thank you for your reply.

I applied Jason's formula to my tables. It returns "NO Match" for every record, including the ones that actually do have a match.

Here is my version of his formula.

=IFERROR(VLOOKUP($B2,'small table' :: $B$2:$B$230,22,FALSE),"NO Match")

I repeated the above formula, except changed 22 to 23 (just as an experiment) and got the exact same results.

Reply

Answer 15

Dec 13, 2012 12:23 PM in response to Jerrold Green1

Hi Wayne and Jerry,

Yes, I also think that something else is going on.

That's how come, before launching this discussion, I changed the cell format of the ID fields in both tables from Automatic to Text and tested for leading and trailing blanks in the ID field (as mentioned in my initial posting).

That's also how come I mentioned that one record without a matching ID in the small table does correctly display LOOKUP's error while all the other records without a matching ID in the small table incorrectly display values in the empty column containing the formula.

I thought that this particular record displaying LOOKUP's error must be different from the others without a matching ID, but I cannot determine what that difference might be. :O(

Regrettably, I cannot post or otherwise distribute my table as is because it contains confidential information.

If you did have a copy of it, what would you look for? If you could point me in that direction, I will investigate.

Thanks again for your time and help on this!

Reply