Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Number Lookup in Matrix
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! |
#2
|
|||
|
|||
assuming that your 3 digit area code is in row 1 between A1:D1 and the phone
number is between B1:D6000 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" wrote in message ... 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! |
#3
|
|||
|
|||
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 "N Harkawat" wrote: assuming that your 3 digit area code is in row 1 between A1:D1 and the phone number is between B1:D6000 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" wrote in message ... 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! |
#4
|
|||
|
|||
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. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Rod" wrote in message ... How can I quickly search a LARGE amount of numbers in the format: 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! |
#5
|
|||
|
|||
N Harkawat wrote...
assuming that your 3 digit area code is in row 1 between A1:D1 and the phone number is between B1:D6000 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. |
#6
|
|||
|
|||
You might also want to try out the play suggested in the other branch ..
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#7
|
|||
|
|||
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")) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#8
|
|||
|
|||
In article .com,
"Harlan Grove" wrote: 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") |
#9
|
|||
|
|||
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 A1:D1? |
#10
|
|||
|
|||
In article .com,
"Harlan Grove" wrote: 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 A1:D1? Ahhh yes! Thanks Harlan! |
#11
|
|||
|
|||
=ISNUMBER(MATCH(F2,INDEX($A$2:$D$6000,0,MATCH(F1,$ A$1:$D$1,0)),0))+0
where A1:D1 houses area codes, A2:D600 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" Rod wrote: 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! |
#12
|
|||
|
|||
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 In article , says... 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! |
#13
|
|||
|
|||
"Tushar Mehta" wrote...
.... 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". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I set up a formula to lookup a value for a condition | Excel Discussion (Misc queries) | |||
lookup more than one cell | Excel Worksheet Functions | |||
Lookup in Matrix | Excel Worksheet Functions | |||
Matrix Query Part II - lookup value | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) |