Banging my head against a spreadsheet problem
February 14, 2020 9:59 AM   Subscribe

I'm trying to match names to phone numbers from 2 different tables in Mac Numbers. It's not working as I think it should. I don't know if the problem lies with the Text Encoding (not even sure what that is, TBH) or something else. Maybe you have solved a similar problem?

I'm using Numbers on my Mac. I have 2 tables in the same spreadsheet, the source and contents of which are:

Table 1: A csv file downloaded from my ATT account with a column containing Phone Numbers (without Names).

Table 2: A file containing Phone Numbers and associated Names extracted from the Contacts on my Mac.

In each of these tables, all the Phone Numbers appear identically as ###.###.####

I would like to match the Phone Numbers in Table 1 to the associated Names in Table 2. I would also like, where there is no match, for the function to return nothing (or the error symbol). Instead, where there is no exact match of Phone Number to Name in the second table the function returns an incorrect Name.

I've tried VLOOKUP, LOOKUP, and MATCH functions.

I've tried reformatting the Phone Numbers in the columns as text, as numbers, or as "Phone" (a custom format) but nothing gives me the result I am looking for.

Can anyone tell me what I'm doing wrong? Does this work in Excel but not in Numbers? Am I SOL?
posted by 6thsense to Computers & Internet (5 answers total)
 
Best answer: Probably a sort order issue.

Backgrounder.
Variations on a theme.
posted by flabdablet at 10:10 AM on February 14, 2020


Best answer: If the lists are unsorted and you're using MATCH(), the third argument (match_type) has to be 0 or the results will be wrong. From the same site as flabdablet suggested, Excel Index and Match Functions is helpful.
posted by scruss at 10:35 AM on February 14, 2020


Best answer: If you're doing a lot of lookups in data with more than a few tens of items, it's always going to be faster to use the variant of these functions that does require its search key column to be sorted, and sort your data by that key. This lets the spreadsheet use a binary search rather than a linear one.

Trying to do a binary search on unsorted data almost always yields results that look completely bizarre until you try to walk through what the binary search is actually doing by hand, at which point the reason why the data need to be sorted soon becomes apparent.
posted by flabdablet at 11:12 AM on February 14, 2020


Best answer: Instead, where there is no exact match of Phone Number to Name in the second table the function returns an incorrect Name.

Just to double-check, when you used VLOOKUP, were you using the version where your syntax was something like VLOOKUP(search-for,columns-range,return-column,close-match) with close-match set to FALSE? I think the default is TRUE, which assumes your data is sorted and it just gives you the first match, which can lead to garbage outputs. My syntax was VLOOKUP(Table 1::B2,Table 2::$B$2:$C$6,2,FALSE).

When I used VLOOKUP on dummy data in Numbers, it returns a red triangle with a ! when the phone number in the numbers table does not appear in the number-name table.
posted by leahwrenn at 11:43 AM on February 14, 2020


Response by poster: These were all lovely and generous answers, but the suggested hacks gave no joy, probably because I had been missing the number "2" as the 3rd argument all along. (In Numbers, there is no prompt for it so unless you know to add it.....)

My intrepid assistant emailed me last night to say she had kept at it and finally found the solution. I post it here as a guidepost for future travelers in Numbers:

VLOOKUP (A2),(contacts::A2:B1364),2,(exact match)

The final sliver of insight was gleaned from the discussion on this Apple Support page.

I am marking all as best answers since I so appreciate everyone's efforts to help.
posted by 6thsense at 5:20 AM on February 15, 2020


« Older Flexible Flight Dates and Other Newbie Travel...   |   Help me make a care package Newer »
This thread is closed to new comments.