4 Replies Latest reply: Nov 26, 2012 11:48 PM by Barry
ltg21 Level 1 Level 1 (0 points)

I have data in Columns A, B & C. Column A has 1st names, Column B has 2nd names, column C has a phone number in it.

 

On a separate sheet I want to be able to type a 2nd name into A2, and a 1st name into B2, and for C2 to bring that person's phone number. So if i typed Jones.....Bob, it would search for jones in column A, then Bob in column B, then bring me his phone number from Column C.

 

Hope i explained that ok.

 

I have tried the following formula, which worked in Excel for the exact same problem, but doesnt work in Numbers. In Numbers the formula only returns the phone number from the 1st guy in the list. This is the formula.....

 

=IF(A2="","",INDEX(C:C,MAX(INDEX(ROW(A:A) * (A:A=A2) * (B:B=B2),))))

 

 

Again, this worked in Excel but does not work in Numbers.

 

Any help would be greatly appreciated

 

Cheers


iMac, iOS 6.0.1
  • 1. Re: Problem with Multiple Indexes
    Wayne Contello Level 6 Level 6 (13,620 points)

    I would add an aux column to your data table like:

     

    Screen Shot 2012-11-26 at 7.33.45 AM.png

    Column C in the table "Phone List" contains the formula:

    Screen Shot 2012-11-26 at 7.34.52 AM.png

    C2=B2&" "&A2

    select C2 and fill down

     

    Then, in the tabele on the right, to locate the specific person in the phone list table use the formula:

    B3=VLOOKUP(B2&" "&B1,Phone List :: C:D, 2)

  • 2. Re: Problem with Multiple Indexes
    ltg21 Level 1 Level 1 (0 points)

    Thanks a lot Wayne, worked like a charm.

  • 3. Re: Problem with Multiple Indexes
    Badunit Level 6 Level 6 (10,815 points)

    One day we might get "array" formulas in Numbers.  They are very handy.

  • 4. Re: Problem with Multiple Indexes
    Barry Level 7 Level 7 (29,180 points)

    Wayne's suggestion is essentially the same as I would offer for the request made—display the phone number is another column on a separate sheet.

     

    But if you just wanted to see and read the telephone number, you could use the reorganize panel to filter the table.

    Here's a sample table, with the reorganize panel located beside it. I've entered one 'rule', but have left the "Show rows" box unchecked so that the whole table is displayed.

    Picture 21.png

    In some cases only the single criterium will be necessary. Here, for example, there are three "John"s in the list, so filtering for John makes it pretty east to pick out the right number:

    Picture 22.png

    If a single term leaves too long a list, add a second by clicking the + sign. Adding "Smith" brings this list down to a single entry:

    Picture 23.png

    Results can be copied from the filtered table, and pasted where you want them.

     

    Regards,

    Barry