4 Replies Latest reply: Nov 26, 2012 11:48 PM by Barry
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
Level 6 (13,620 points)

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

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
Level 1 (0 points)

Thanks a lot Wayne, worked like a charm.

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

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

• ###### 4. Re: Problem with Multiple Indexes
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.

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:

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:

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

Regards,

Barry