Number Lookup in Matrix

[Rod]

How can I quickly search a LARGE amount of numbers in the format:

a1 a2 a3 a4 a5 a6...
n1 n6 n11
n2 n7 ...
n3 n8
n4 n9
n5 n10

where a# are three digit area codes and n# are 7 digit phone numbers. The
area code col to be search will be determined and feed from another cell.
Once this formula sees there is an area code it should check the area code
headings for a match then serach that area code col to find a phone number
match if one exist. A simple "Y" or "N" return value would suffice as a
result of the fomula.

Thank You!

 

[N Harkawat]

assuming that your 3 digit area code is in row 1 between A11 and the phone
number is between B16000
and "another cell" holding the area code is cell F1 and phone number in F2

=IF(ISNA(INDEX(OFFSET(A2,,MATCH(F1,$A$1:$D$1,0)-1,6000),MATCH(F2,OFFSET(A2,,MATCH(F1,$A$1:$D$1,0)-1,6000),0))),"N","Y")

will give a Y or N depending whether phne number exists

If your "another cell" holds both area code and tel # in 1 single cell say
in cell F1 then use

=IF(ISNA(INDEX(OFFSET(A2,,MATCH(left(F1,3),$A$1:$D$1,0)-1,6000),MATCH(F2,OFFSET(A2,,MATCH(right(F1,7),$A$1:$D$1,0)-1,6000),0))),"N","Y")

 

[Rod]

Not quit sure what to change. The area codes are as follows:
A1 B1 C1...
Area1 Area2 Area3...

The phone numbers are as follows:
A2 B2 C2...
Phone1 Phone2 Phone3...
PhoneX PhoneY PhoneZ

Area codes and numbers will always be separated as above.

If I enter Area2 and then PhoneY I should get a "Y" as a result. If I enter
Area1 and PhoneZ I should get a "N"

Thanks

 

[Max]

One play to try ..

Assuming the source table below is in Sheet1,
with the area codes in A1, B1, C1 ... etc

a1 a2 a3 a4 a5 a6...
n1 n6 n11
n2 n7 ...
n3 n8
n4 n9
n5 n10

In Sheet2
------
Assume the area codes will be input in col A and the corresponding phone
numbers in col B, from row1 down

Put in C1:

=IF(OR(A1="",B1=""),"",IF(ISNUMBER(MATCH(B1,OFFSET(Sheet1!A:A,,MATCH(A1,Shee
t1!1:1,0)-1),0)),"Y","N"))

Copy C1 down
(can copy down ahead of expected data input in cols A and B)

Col C will return the desired results, i.e. either "Y" or "N" depending on
the values in cols A and B.

 

[Harlan Grove]

N Harkawat wrote...

assuming that your 3 digit area code is in row 1 between A11 and the phone
number is between B16000
and "another cell" holding the area code is cell F1 and phone number in F2

=IF(ISNA(INDEX(OFFSET(A2,,MATCH(F1,$A$1:$D$1,0)-1,6000),
MATCH(F2,OFFSET(A2,,MATCH(F1,$A$1:$D$1,0)-1,6000),0))),"N","Y")

will give a Y or N depending whether phne number exists

....

The volatile OFFSET call isn't needed for this. It could be done with

=IF(ISNUMBER(1/COUNTIF(INDEX($A$2:$D$6000,0,MATCH(F1,$A$1:$D$1,0)),F2))
,"Y","N")

Another advantage is that if rows with new phone numbers were inserted
between rows 2 and 6000, the range reference in the INDEX formula will
automatically expand to include them. The OFFSET formula would require
manually changing the 6000 figures.

 

[Max]

You might also want to try out the play suggested in the other branch ..

 

[Max]

Sorry, slight correction to the formula in C1 in Sheet2
(forgot to fix the Sheet1 row1 reference)

Put instead in C1, and copy down:

=IF(OR(A1="",B1=""),"",IF(ISNUMBER(MATCH(B1,OFFSET(Sheet1!A:A,,MATCH(A1,Shee
t1!$1:$1,0)-1),0)),"Y","N"))

 

[Domenic]

The volatile OFFSET call isn't needed for this. It could be done with

=IF(ISNUMBER(1/COUNTIF(INDEX($A$2:$D$6000,0,MATCH(F1,$A$1:$D$1,0)),F2))
,"Y","N")

Another advantage is that if rows with new phone numbers were inserted
between rows 2 and 6000, the range reference in the INDEX formula will
automatically expand to include them. The OFFSET formula would require
manually changing the 6000 figures.

Or, you can eliminate the ISNUMBER function...

=IF(COUNTIF(INDEX($A$2:$D$6000,0,MATCH(F1,$A$1:$D$1,0)),F2)>0,"Y","N")

 

[Harlan Grove]

Domenic wrote...
....

Or, you can eliminate the ISNUMBER function...

=IF(COUNTIF(INDEX($A$2:$D$6000,0,MATCH(F1,$A$1:$D$1,0)),F2)>0,"Y","N")

And what does this return when the area code in F1 doesn't appear in
A11?

 

[Domenic]

Domenic wrote...
...

And what does this return when the area code in F1 doesn't appear in
A11?

Ahhh yes! Thanks Harlan!

 

[Aladin Akyurek]

=ISNUMBER(MATCH(F2,INDEX($A$2:$D$6000,0,MATCH(F1,$A$1:$D$1,0)),0))+0

where A11 houses area codes, A2600 phone numbers, F1 an area code of
interest, and F2 a phone number of interest.

Custom format the formula cell as:

[=0]"N";[=1]"Y"

 

[Tushar Mehta]

If I were in your shoes, I'd do one of two things neither of which
involve cramming a complex formula into a single cell.

The benefit of the method described below is that the worksheet will be
easier to understand and maintain. It will also implement the logic as
you described it, i.e., in two steps. In addition, it will provide you
will additional information should you need it.

Suppose your table is laid out in sheet1 and you are doing your
analysis on sheet2. For my tests, the Sheet1 data were in A1:C3. In
sheet2, the area code you want to look up was in B2 and the number in
B3.

1) Then, in some cell, say C2, show the result of step 1 of your
intent, i.e., the result of the area code lookup:
=MATCH(B2,Sheet1!$A$1:$C$1,0).

Now, tackle the 2nd step of your task. In some cell, say C3, enter:
=IF(ISNA(MATCH(B3,INDEX(Sheet1!$A$2:$C$3,0,Sheet2!C2),0)),"N","Y")

2) Personally, I would go with this variant. Instead of directly
putting Y/N in C3, put the value of the look up result. So, suppose
the value of the area code look up is now in D2:
=MATCH(B2,Sheet1!$A$1:$C$1,0) Then, in D3 enter the result of the
phone number lookup: =MATCH(B3,INDEX(Sheet1!$A$2:$C$3,0,Sheet2!D2),0)
Finally, in D4, enter: =IF(ISNA(D3),"N","Y")

The benefit of the approach should be obvious. The worksheet closely
mimics the stated business problem. The formulas are simple and you
know the results of the intermediate steps -- column of the area code
match and the row of the phone number match, which makes debugging that
much easier. Of course, you can also easily label the intermediate
results by typing in text into an adjacent cell.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Harlan Grove]

The benefit of the approach should be obvious. The worksheet closely
mimics the stated business problem. The formulas are simple and you
know the results of the intermediate steps -- column of the area code
match and the row of the phone number match, which makes debugging
that much easier. Of course, you can also easily label the
intermediate results by typing in text into an adjacent cell.

....

This is all very nice, but phone number lookups are ideally a database task.
The most sensible way to deal with the business problem is to use the tool
best suited to the task. The OP's task is a form of misuse of spreadsheets.

There's also the matter that the OP's phone number layout is a poor data
structure. Area codes as column headings with local phone numbers below is
much less useful than a single column of area codes and phone numbers
combined, sorted in ascending order. Phone numbers within each area code
would still be grouped, but a single fairly simple MATCH call (separately
entered area code and phone number would need to be concatenated - hopefully
you wouldn't recommend doing that alone in a separate cell) would be able to
determine whether the number exitst.

BTW, Aladin's approach is best, though I might change the custom number
format to "Y";"Y";"N".