You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

IFERROR (VLOOKUP) on a concatenated field

In a Numbers 09 file, I need to find student records in two tables that have identical values in two specific columns and discrepant values in a specific third column.


Because of difficulties getting an IFERROR (VLOOKUP) formula to function properly, I tried to simplify by concatenating the three columns in a new column in both tables using the formula


=CONCATENATE(A2,S2,T2)


then copy-pasting as values, and making sure the column's cell format was Text.


The following formula works properly.


=VLOOKUP(U2,TABLE1 :: $U$2:$U$2582,1,0)


where column U in Tables 1 and 2 contains concatenated data from the three columns of interest. This formula returns correct values for records that match between the two tables' concatenated column, and returns an error for records that do not match; I determined this by spot checking records in the two tables (Table 2 has almost 1500 records; Table 1 almost 2600).


The following formula, under identical conditions, returns "Not found" for every record.


=IFERROR(VLOOKUP(U2,TABLE1 :: $A$2:$U$2582,21,FALSE),"Not found")


where 21 is the column U containing concatenated values in Table 1.


If I copy-paste cell U2's value in Table 2 to the same cell in Table 1, the IFERROR forumla in Table 2 still returns "Not found."


How come the above VLOOKUP formula returns correct values while the IFERROR formula does not? What am I doing improperly?


Thanks in advance!

iMac, OS X Mountain Lion (10.8.4)

Posted on Dec 10, 2013 8:20 AM

Reply
21 replies

Dec 10, 2013 10:27 AM in response to Barry Fass-Holmes

1. The first formula worked because the range you specified had U for its left column.


2. The return column specifies the result of the lookup. If the lookup finds a match, the value from the return column in the row of the match is what shows up in the cell containing the formula.


I sort of get the idea that you don't really want a lookup at all, just a true or false to indicate whether a match was found.

Dec 10, 2013 10:32 AM in response to Badunit

Thanks for clarifying.


I just tested the following formula, and it also returned correct values rather than all "Not found" (for the same reason as in your last reply).


=IFERROR(VLOOKUP(U2,Table 1 :: $U$2:$U$2582,1,FALSE),"Not found")


In the future, I will use the above structure for VLOOKUP to avoid getting incorrect answers and confusion.


🙂

Dec 10, 2013 10:37 AM in response to Jeff Shenk

Thanks for clarifying.


Got it, and that's also evidently how come the following formula worked.


=IFERROR(VLOOKUP(U2,Table 1 :: $U$2:$U$2582,1,FALSE),"Not found")


In the future, I will use the above formula's structure to avoid getting incorrect returns and confusion.


Yes, that's correct—a true or false to indicate whether a discrepancy was found.


From my initial posting above…


In a Numbers 09 file, I need to find student records in two tables that have identical values in two specific columns and discrepant values in a specific third column.

IFERROR (VLOOKUP) on a concatenated field

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.