Apple Event: May 7th at 7 am PT

Looks like no one’s replied in a while. To start the conversation again, simply ask a new 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

29 replies

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

You should be able to make a sanitized version without any sensistive information that still dsmonstrates the problem. That is all we need. I am wanting to see some of the intermediate results used in the formula. So playing with this directly would be easier.

Jeff Shenk

Level 4

2,993 points

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

Naturally it returns "NO Match;" you asked for the result in the 22nd column of a single column search range.

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

should get the result.

Jeff Shenk

Level 4

2,993 points

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

I would guess that the one result that got an error was looking for an ID that was lower than any of the ones in the search range (no "close match").

Jeff Shenk

Level 4

2,993 points

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

Jerry,

Doesn't MATCH do a close match unless you give it a third parameter?

Dec 13, 2012 1:02 PM in response to Jeff Shenk

Dec 13, 2012 1:04 PM in response to Wayne Contello

Hi Wayne,

You could be right that I should be able to.

But I won't. Too risky.

I think that one (or both) of the tables has (have) some sort of corruption, and maybe rebuilding it (them) from scratch might eliminate such a corruption.

If you have any better ideas, I'll try making time to test them.

Jeff Shenk

Level 4

2,993 points

Dec 13, 2012 1:16 PM in response to Wayne Contello

Wayne,

From the iWork '09 Missing Manual:

"There's a catch, though, …LOOKUP doesn't know how to do an exact match. It always uses the close-match method…"

Dec 13, 2012 1:20 PM in response to Jeff Shenk

Hi Jeff,

Thanks, that's testable (although it depends upon how these formulae define "close").

The one record in the large table for which LOOKUP correctly returned an error has an ID with a lower absolute value than the lowest absolute value ID in the small table. Specifically, the IDs consists of eight digits. If I subtract the ID for which LOOKUP correctly returned an error from the nearest ID in the smaller table, the difference is 6,997.

I did a similar check on one of the records in the large table that does not have a matching ID in the small table but LOOKUP improperly copied a value from the small table to the large table.

I subtracted the ID for that record from the IDs of the seven records in the small table that have the same value that LOOKUP improperly copied to the empty column containing the LOOKUP formula in the large table. The closest difference (of the seven records' subtractions) is 16.

These results indicate that your guess could be correct, but it depends upon what constitutes a "close match."

Dec 13, 2012 1:23 PM in response to Jeff Shenk

Jeff,

Your point about "close match" leads me to think that, in the future, I should avoid using LOOKUP for matching purposes and instead find some other approach that relies on exact match (hence your original recommendation, and Jason's too, of using VLOOKUP).

However, as mentioned in my reply to Jason above, my translation of his formula returned "NO Match" for every record including ones that actually do have a match.

:O(

Dec 13, 2012 1:41 PM in response to Jeff Shenk

Jeff,

There are matching method parameters in Match:

Find largest value (1 or omitted): Find the cell with the largest value less than or equal to search-for. Wildcards can’t be used in search-for.

Find value (0): Find the first cell with a value that exactly matches search-for. Wildcards can be used in search-for.

Find smallest value (–1): Find the cell with the smallest value greater than or equal to search-for. Wildcards can’t be used in search-for.

Since I never use this method for anything other than strings, I haven't had a problem, or didn't notice it if I did. That's why I'd like to see the sample file. I wouldn't be a problem to add the "0" parameter to force the exact match.

Jerry

Jeff Shenk

Level 4

2,993 points

Dec 13, 2012 1:50 PM in response to Barry Fass-Holmes

Barry,

Check my comment above: for VLOOKUP, the range has to include both the search terms and the answers.

Dec 13, 2012 1:56 PM in response to Jeff Shenk

Hi Jeff,

Bingo!

That did the trick, and it returned correct values for the records that do have a match and returned the errors for ones that do not have a match.

Many thanks. I award you a checkmark and Jason gets a star.

Barry

jaxjason

Level 5

5,047 points

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

I appologize if it came across wrong, I wasnt implying you hadnt searched, but was pointing you to the formula pdf that has every formula available and can be kept at the ready in iBooks.

The way vlookup works is you "look up" a value in the left hand column of a range and bring back some value in one of the columns on that row the match is found. The range you use is not being compared, but is the list of columns you might want to bring back from when a match is found in the left hand column.

So if you know the name Jason is found in Column B (first names), and you want the value in the C column brought back, you would feed it the range "B1:C100" (or whatever your last row is, you can also reference the whole column "B:C") and ask for the second column (B being the first column).

=vlookup(X1,$B:$C,2,false)

So in your example, we are looking for whatever is located in Column B for this row, look in the "small table" columns B:W (which holds all my data, column B being where my item I am using for look up is), and when you find a match, go over to the 22nd column in that range i gave (B2:W230) on that row adn bring its value back.

Does that help clear it up? I know vlookups confuse alot of people i get at least a half dozen questions a month on it where i work.

Let me know if you need more examples, I can throw a spreadsheet together and email it to you.

Jason

Dec 16, 2012 1:19 PM in response to jaxjason

Hi Jason,

Thanks again for your time and helpful posting.

Yes, that clears it up!

Barry

LOOKUP returning unexpected results