|
Multiple table lookup
A B
1 Name Code 2 London 301 3 Paris 302 4 Rome 303 5 Moscow 304 6 Madrid 305 7 Berlin 306 9 Name Code 10 Brazilia 307 11 Buenos Aires 308 12 Santiago 309 13 Montevide 310 14 Quito 311 15 Lima 312 D E 1 Date Code 2 17/3/2010 301 3 22/3/2010 306 3 23/6/2010 312 4 14/5/2010 305 5 22/9/2010 302+301 6 25/10/2010 308 7 1/3/2010 311 8 9/11/2010 301 9 6/5/2010 307 10 17/7/2010 302+305 11 28/7/2010 304 12 6/8/2010 309 13 31/12/2010 310 14 2/2/2010 301 15 9/2/2010 303 16 5/6/2010 307 17 1/10/2010 307+312 What i want to do is that when i enter a code (let's say 301) in the cell G2, then in cells O3:O15 appear the dates corresponding to the codes 300,301,302,303,304,305 & 306. And if i enter 312, then in cells O3:O15 appear the dates corresponding to the codes 307,308,309,310,311 & 312 ( taking into account that "301" that i entered in cell G2 is a part of "302+301" in cell E6) I entered 3 formulas that have solved a part of the problem, but the last problem is that the date corresponding to "302+301" was not included coz it's not exactly what I entered in G2.. So could u please modify my formulas or create others to solve that problem ?? Here are my formulas In O1 =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},MATCH(G 2,CodeListA,0),MATCH(G2,CodeListB,0)),CHOOSE({1,2} ,"CodeListA","CodeListB")) In O2 (ctrl+shift+enter) {=SUM(IF(ISNUMBER(MATCH(E2:E18,INDIRECT(O1),0)),1) )} In Range O3:O15 (ctrl+shift+enter) {=IF(ISNA($O$1),"",IF(ROWS($O$3:O3)<=$O$2,INDEX($D $2:$D$18,SMALL(IF(ISNUMBER(MATCH($E$2:$E$18,INDIRE CT($O$1),0)),ROW($D$2:$D$18)-ROW($D$2)+1),ROWS($O$3:O3))),""))} And these are the results N O 1 Range : CodeListA Where CodeListA is the range B2:B7 2 No. of dates 7 While it has to be 9 !! 3 Dates: 17/3/2010 22/3/2010 14/5/2010 9/11/2010 28/7/2010 2/2/2010 9/2/2010 Thank you |
Multiple table lookup
On the surface this would seem like a fairly straightforward lookup/data
extraction but in reality it's a bit complex! What makes it complex are the cells where there are multiple codes: 302+301 302+305 307+312 CodeListA refers to B$2:B$7 CodeListB refers to B$10:B$15 Create this additional name: LookupCodes Refers to: =TRANSPOSE(INDIRECT(Sheet1!$N$2)) Use the appropriate sheet name. G2 = some code like 301 Enter this formula in N2. This will return the name of the range that the code number in G2 is located in. =IF(COUNTIF(CodeListA,G2),"CodeListA",IF(COUNTIF(C odeListB,G2),"CodeListB","")) Enter this array formula** in O2. This will return the count of records that meet the criteria. =IF(N2="","",SUM(--(MMULT(--(ISNUMBER(SEARCH(LookupCodes,E2:E18))),ROW(INDIREC T(N2))^0)0))) Enter this array formula** in N3. This will extract the dates that meet the criteria. =IF(N2="","",IF(ROWS(N$3:N3)O$2,"",INDEX(D:D,SMAL L(IF(MMULT(--(ISNUMBER(SEARCH(LookupCodes,E$2:E$18))),ROW(INDIR ECT(N$2))^0)0,ROW(E$2:E$18)),ROWS(N$3:N3))))) Format as Date. Copy down until you get blanks. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "tornado1981" wrote in message ... A B 1 Name Code 2 London 301 3 Paris 302 4 Rome 303 5 Moscow 304 6 Madrid 305 7 Berlin 306 9 Name Code 10 Brazilia 307 11 Buenos Aires 308 12 Santiago 309 13 Montevide 310 14 Quito 311 15 Lima 312 D E 1 Date Code 2 17/3/2010 301 3 22/3/2010 306 3 23/6/2010 312 4 14/5/2010 305 5 22/9/2010 302+301 6 25/10/2010 308 7 1/3/2010 311 8 9/11/2010 301 9 6/5/2010 307 10 17/7/2010 302+305 11 28/7/2010 304 12 6/8/2010 309 13 31/12/2010 310 14 2/2/2010 301 15 9/2/2010 303 16 5/6/2010 307 17 1/10/2010 307+312 What i want to do is that when i enter a code (let's say 301) in the cell G2, then in cells O3:O15 appear the dates corresponding to the codes 300,301,302,303,304,305 & 306. And if i enter 312, then in cells O3:O15 appear the dates corresponding to the codes 307,308,309,310,311 & 312 ( taking into account that "301" that i entered in cell G2 is a part of "302+301" in cell E6) I entered 3 formulas that have solved a part of the problem, but the last problem is that the date corresponding to "302+301" was not included coz it's not exactly what I entered in G2.. So could u please modify my formulas or create others to solve that problem ?? Here are my formulas In O1 =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},MATCH(G 2,CodeListA,0),MATCH(G2,CodeListB,0)),CHOOSE({1,2} ,"CodeListA","CodeListB")) In O2 (ctrl+shift+enter) {=SUM(IF(ISNUMBER(MATCH(E2:E18,INDIRECT(O1),0)),1) )} In Range O3:O15 (ctrl+shift+enter) {=IF(ISNA($O$1),"",IF(ROWS($O$3:O3)<=$O$2,INDEX($D $2:$D$18,SMALL(IF(ISNUMBER(MATCH($E$2:$E$18,INDIRE CT($O$1),0)),ROW($D$2:$D$18)-ROW($D$2)+1),ROWS($O$3:O3))),""))} And these are the results N O 1 Range : CodeListA Where CodeListA is the range B2:B7 2 No. of dates 7 While it has to be 9 !! 3 Dates: 17/3/2010 22/3/2010 14/5/2010 9/11/2010 28/7/2010 2/2/2010 9/2/2010 Thank you -- tornado1981 |
Multiple table lookup
Tweak...
=IF(N2="","",IF(ROWS(N$3:N3)O$2,"",... Make N2 row absolute. =IF(N$2="","",IF(ROWS(N$3:N3)O$2,"",... -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... On the surface this would seem like a fairly straightforward lookup/data extraction but in reality it's a bit complex! What makes it complex are the cells where there are multiple codes: 302+301 302+305 307+312 CodeListA refers to B$2:B$7 CodeListB refers to B$10:B$15 Create this additional name: LookupCodes Refers to: =TRANSPOSE(INDIRECT(Sheet1!$N$2)) Use the appropriate sheet name. G2 = some code like 301 Enter this formula in N2. This will return the name of the range that the code number in G2 is located in. =IF(COUNTIF(CodeListA,G2),"CodeListA",IF(COUNTIF(C odeListB,G2),"CodeListB","")) Enter this array formula** in O2. This will return the count of records that meet the criteria. =IF(N2="","",SUM(--(MMULT(--(ISNUMBER(SEARCH(LookupCodes,E2:E18))),ROW(INDIREC T(N2))^0)0))) Enter this array formula** in N3. This will extract the dates that meet the criteria. =IF(N2="","",IF(ROWS(N$3:N3)O$2,"",INDEX(D:D,SMAL L(IF(MMULT(--(ISNUMBER(SEARCH(LookupCodes,E$2:E$18))),ROW(INDIR ECT(N$2))^0)0,ROW(E$2:E$18)),ROWS(N$3:N3))))) Format as Date. Copy down until you get blanks. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "tornado1981" wrote in message ... A B 1 Name Code 2 London 301 3 Paris 302 4 Rome 303 5 Moscow 304 6 Madrid 305 7 Berlin 306 9 Name Code 10 Brazilia 307 11 Buenos Aires 308 12 Santiago 309 13 Montevide 310 14 Quito 311 15 Lima 312 D E 1 Date Code 2 17/3/2010 301 3 22/3/2010 306 3 23/6/2010 312 4 14/5/2010 305 5 22/9/2010 302+301 6 25/10/2010 308 7 1/3/2010 311 8 9/11/2010 301 9 6/5/2010 307 10 17/7/2010 302+305 11 28/7/2010 304 12 6/8/2010 309 13 31/12/2010 310 14 2/2/2010 301 15 9/2/2010 303 16 5/6/2010 307 17 1/10/2010 307+312 What i want to do is that when i enter a code (let's say 301) in the cell G2, then in cells O3:O15 appear the dates corresponding to the codes 300,301,302,303,304,305 & 306. And if i enter 312, then in cells O3:O15 appear the dates corresponding to the codes 307,308,309,310,311 & 312 ( taking into account that "301" that i entered in cell G2 is a part of "302+301" in cell E6) I entered 3 formulas that have solved a part of the problem, but the last problem is that the date corresponding to "302+301" was not included coz it's not exactly what I entered in G2.. So could u please modify my formulas or create others to solve that problem ?? Here are my formulas In O1 =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},MATCH(G 2,CodeListA,0),MATCH(G2,CodeListB,0)),CHOOSE({1,2} ,"CodeListA","CodeListB")) In O2 (ctrl+shift+enter) {=SUM(IF(ISNUMBER(MATCH(E2:E18,INDIRECT(O1),0)),1) )} In Range O3:O15 (ctrl+shift+enter) {=IF(ISNA($O$1),"",IF(ROWS($O$3:O3)<=$O$2,INDEX($D $2:$D$18,SMALL(IF(ISNUMBER(MATCH($E$2:$E$18,INDIRE CT($O$1),0)),ROW($D$2:$D$18)-ROW($D$2)+1),ROWS($O$3:O3))),""))} And these are the results N O 1 Range : CodeListA Where CodeListA is the range B2:B7 2 No. of dates 7 While it has to be 9 !! 3 Dates: 17/3/2010 22/3/2010 14/5/2010 9/11/2010 28/7/2010 2/2/2010 9/2/2010 Thank you -- tornado1981 |
Quote:
i got some errors when i apply ur codes Thank u |
Hi valko,
Finally i did it thanks so much for ur help that was awesome but i got another problem what if i want to search in two columns and not just one ( column E & F) ? is that possible ? |
Multiple table lookup
Here's a small sample file that demonstrates this..
Tornado1981.xls 17kb http://cjoint.com/?efuMzriA6o -- Biff Microsoft Excel MVP "tornado1981" wrote in message ... T. Valko;942822 Wrote: Tweak... - =IF(N2="","",IF(ROWS(N$3:N3)O$2,"",...- Make N2 row absolute. =IF(N$2="","",IF(ROWS(N$3:N3)O$2,"",... -- Biff Microsoft Excel MVP "T. Valko" wrote in message ...- On the surface this would seem like a fairly straightforward lookup/data extraction but in reality it's a bit complex! What makes it complex are the cells where there are multiple codes: - 302+301 302+305 307+312- CodeListA refers to B$2:B$7 CodeListB refers to B$10:B$15 Create this additional name: LookupCodes Refers to: =TRANSPOSE(INDIRECT(Sheet1!$N$2)) Use the appropriate sheet name. G2 = some code like 301 Enter this formula in N2. This will return the name of the range that the code number in G2 is located in. =IF(COUNTIF(CodeListA,G2),"CodeListA",IF(COUNTIF(C odeListB,G2),"CodeListB","")) Enter this array formula** in O2. This will return the count of records that meet the criteria. =IF(N2="","",SUM(--(MMULT(--(ISNUMBER(SEARCH(LookupCodes,E2:E18))),ROW(INDIREC T(N2))^0)0))) Enter this array formula** in N3. This will extract the dates that meet the criteria. =IF(N2="","",IF(ROWS(N$3:N3)O$2,"",INDEX(D:D,SMAL L(IF(MMULT(--(ISNUMBER(SEARCH(LookupCodes,E$2:E$18))),ROW(INDIR ECT(N$2))^0)0,ROW(E$2:E$18)),ROWS(N$3:N3))))) Format as Date. Copy down until you get blanks. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "tornado1981" wrote in message ...- A B 1 Name Code 2 London 301 3 Paris 302 4 Rome 303 5 Moscow 304 6 Madrid 305 7 Berlin 306 9 Name Code 10 Brazilia 307 11 Buenos Aires 308 12 Santiago 309 13 Montevide 310 14 Quito 311 15 Lima 312 D E 1 Date Code 2 17/3/2010 301 3 22/3/2010 306 3 23/6/2010 312 4 14/5/2010 305 5 22/9/2010 302+301 6 25/10/2010 308 7 1/3/2010 311 8 9/11/2010 301 9 6/5/2010 307 10 17/7/2010 302+305 11 28/7/2010 304 12 6/8/2010 309 13 31/12/2010 310 14 2/2/2010 301 15 9/2/2010 303 16 5/6/2010 307 17 1/10/2010 307+312 What i want to do is that when i enter a code (let's say 301) in the cell G2, then in cells O3:O15 appear the dates corresponding to the codes 300,301,302,303,304,305 & 306. And if i enter 312, then in cells O3:O15 appear the dates corresponding to the codes 307,308,309,310,311 & 312 ( taking into account that "301" that i entered in cell G2 is a part of "302+301" in cell E6) I entered 3 formulas that have solved a part of the problem, but the last problem is that the date corresponding to "302+301" was not included coz it's not exactly what I entered in G2.. So could u please modify my formulas or create others to solve that problem ?? Here are my formulas In O1 =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},MATCH(G 2,CodeListA,0),MATCH(G2,CodeListB,0)),CHOOSE({1,2} ,"CodeListA","CodeListB")) In O2 (ctrl+shift+enter) {=SUM(IF(ISNUMBER(MATCH(E2:E18,INDIRECT(O1),0)),1) )} In Range O3:O15 (ctrl+shift+enter) {=IF(ISNA($O$1),"",IF(ROWS($O$3:O3)<=$O$2,INDEX($D $2:$D$18,SMALL(IF(ISNUMBER(MATCH($E$2:$E$18,INDIRE CT($O$1),0)),ROW($D$2:$D$18)-ROW($D$2)+1),ROWS($O$3:O3))),""))} And these are the results N O 1 Range : CodeListA Where CodeListA is the range B2:B7 2 No. of dates 7 While it has to be 9 !! 3 Dates: 17/3/2010 22/3/2010 14/5/2010 9/11/2010 28/7/2010 2/2/2010 9/2/2010 Thank you -- tornado1981- - Thanks so much valko for ur help .. but would u please attach an excel sheet for explanation ? i got some errors when i apply ur codes Thank u -- tornado1981 |
Thanks so much T. Valko .. That's really awesome .. But could we modify these codes to search in columns E & F instead of Just Column E ??
the sheet would be like that http://cjoint.com/data/efv6hthN8f.htm Quote:
|
Thanks so much T. Valko .. That's really awesome .. But could we modify these codes to search in columns E & F instead of Just Column E ??
the sheet would be like that http://cjoint.com/data/efv6hthN8f.htm Quote:
|
Multiple table lookup
what if i want to search in two columns
and not just one ( column E & F) ? What's in column F? -- Biff Microsoft Excel MVP "tornado1981" wrote in message ... Hi valko, Finally i did it thanks so much for ur help that was awesome but i got another problem what if i want to search in two columns and not just one ( column E & F) ? is that possible ? -- tornado1981 |
Multiple table lookup
Tornado1981(2).xls
http://cjoint.com/?egfrN0oCsQ -- Biff Microsoft Excel MVP "tornado1981" wrote in message ... Thanks so much T. Valko .. That's really awesome .. But could we modify these codes to search in columns E & F instead of Just Column E ?? the sheet would be like that http://cjoint.com/data/efv6hthN8f.htm T. Valko;943026 Wrote: Here's a small sample file that demonstrates this.. Tornado1981.xls 17kb http://cjoint.com/?efuMzriA6o -- Biff Microsoft Excel MVP "tornado1981" wrote in message ...- T. Valko;942822 Wrote:- Tweak... -- =IF(N2="","",IF(ROWS(N$3:N3)O$2,"",...-- Make N2 row absolute. =IF(N$2="","",IF(ROWS(N$3:N3)O$2,"",... -- Biff Microsoft Excel MVP "T. Valko" wrote in message ...-- On the surface this would seem like a fairly straightforward- lookup/data- extraction but in reality it's a bit complex! What makes it complex- are- the cells where there are multiple codes: - 302+301 302+305 307+312- CodeListA refers to B$2:B$7 CodeListB refers to B$10:B$15 Create this additional name: LookupCodes Refers to: =TRANSPOSE(INDIRECT(Sheet1!$N$2)) Use the appropriate sheet name. G2 = some code like 301 Enter this formula in N2. This will return the name of the range that- the- code number in G2 is located in. - =IF(COUNTIF(CodeListA,G2),"CodeListA",IF(COUNTIF(C odeListB,G2),"CodeListB",""))- Enter this array formula** in O2. This will return the count of- records- that meet the criteria. - =IF(N2="","",SUM(--(MMULT(--(ISNUMBER(SEARCH(LookupCodes,E2:E18))),ROW(INDIREC T(N2))^0)0)))- Enter this array formula** in N3. This will extract the dates that- meet- the criteria. - =IF(N2="","",IF(ROWS(N$3:N3)O$2,"",INDEX(D:D,SMAL L(IF(MMULT(--(ISNUMBER(SEARCH(LookupCodes,E$2:E$18))),ROW(INDIR ECT(N$2))^0)0,ROW(E$2:E$18)),ROWS(N$3:N3)))))- Format as Date. Copy down until you get blanks. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and- the- SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "tornado1981" wrote in message- - ...- A B 1 Name Code 2 London 301 3 Paris 302 4 Rome 303 5 Moscow 304 6 Madrid 305 7 Berlin 306 9 Name Code 10 Brazilia 307 11 Buenos Aires 308 12 Santiago 309 13 Montevide 310 14 Quito 311 15 Lima 312 D E 1 Date Code 2 17/3/2010 301 3 22/3/2010 306 3 23/6/2010 312 4 14/5/2010 305 5 22/9/2010 302+301 6 25/10/2010 308 7 1/3/2010 311 8 9/11/2010 301 9 6/5/2010 307 10 17/7/2010 302+305 11 28/7/2010 304 12 6/8/2010 309 13 31/12/2010 310 14 2/2/2010 301 15 9/2/2010 303 16 5/6/2010 307 17 1/10/2010 307+312 What i want to do is that when i enter a code (let's say 301) in- the- cell G2, then in cells O3:O15 appear the dates corresponding to the codes 300,301,302,303,304,305 & 306. And if i enter 312, then in cells O3:O15 appear the dates- corresponding- to the codes 307,308,309,310,311 & 312 ( taking into account that- "301"- that i entered in cell G2 is a part of "302+301" in cell E6) I entered 3 formulas that have solved a part of the problem, but- the- last problem is that the date corresponding to "302+301" was not included coz it's not exactly what I entered in G2.. So could u- please- modify my formulas or create others to solve that problem ?? Here are my formulas In O1 - =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},MATCH(G 2,CodeListA,0),MATCH(G2,CodeListB,0)),CHOOSE({1,2}- ,"CodeListA","CodeListB")) In O2 (ctrl+shift+enter) {=SUM(IF(ISNUMBER(MATCH(E2:E18,INDIRECT(O1),0)),1) )} In Range O3:O15 (ctrl+shift+enter) - {=IF(ISNA($O$1),"",IF(ROWS($O$3:O3)<=$O$2,INDEX($D $2:$D$18,SMALL(IF(ISNUMBER(MATCH($E$2:$E$18,INDIRE- CT($O$1),0)),ROW($D$2:$D$18)-ROW($D$2)+1),ROWS($O$3:O3))),""))} And these are the results N O 1 Range : CodeListA Where CodeListA is the range B2:B7 2 No. of dates 7 While it has to be 9 !! 3 Dates: 17/3/2010 22/3/2010 14/5/2010 9/11/2010 28/7/2010 2/2/2010 9/2/2010 Thank you -- tornado1981- --- Thanks so much valko for ur help .. but would u please attach an excel sheet for explanation ? i got some errors when i apply ur codes Thank u -- tornado1981 - -- tornado1981 |
Hi Valko
Would u please take a look at that file please Thank u http://cjoint.com/?ehuoyrWg8V Quote:
|
Multiple table lookup
Ok, you say you want to highlight the dates associated with N2.
Why isn't 10/25/2010 (or 25/10/2010) highlighted? -- Biff Microsoft Excel MVP "tornado1981" wrote in message ... Hi Valko Would u please take a look at that file please Thank u http://cjoint.com/?ehuoyrWg8V T. Valko;943145 Wrote: Tornado1981(2).xls http://cjoint.com/?egfrN0oCsQ -- Biff Microsoft Excel MVP "tornado1981" wrote in message ...- Thanks so much T. Valko .. That's really awesome .. But could we modify these codes to search in columns E & F instead of Just Column E ?? the sheet would be like that http://cjoint.com/data/efv6hthN8f.htm T. Valko;943026 Wrote:- Here's a small sample file that demonstrates this.. Tornado1981.xls 17kb http://cjoint.com/?efuMzriA6o -- Biff Microsoft Excel MVP "tornado1981" wrote in message ...-- T. Valko;942822 Wrote:- Tweak... -- =IF(N2="","",IF(ROWS(N$3:N3)O$2,"",...-- Make N2 row absolute. =IF(N$2="","",IF(ROWS(N$3:N3)O$2,"",... -- Biff Microsoft Excel MVP "T. Valko" wrote in message ...-- On the surface this would seem like a fairly straightforward- lookup/data- extraction but in reality it's a bit complex! What makes it- complex-- are- the cells where there are multiple codes: - 302+301 302+305 307+312- CodeListA refers to B$2:B$7 CodeListB refers to B$10:B$15 Create this additional name: LookupCodes Refers to: =TRANSPOSE(INDIRECT(Sheet1!$N$2)) Use the appropriate sheet name. G2 = some code like 301 Enter this formula in N2. This will return the name of the range- that-- the- code number in G2 is located in. - - =IF(COUNTIF(CodeListA,G2),"CodeListA",IF(COUNTIF(C odeListB,G2),"CodeListB",""))-- Enter this array formula** in O2. This will return the count of- records- that meet the criteria. - - =IF(N2="","",SUM(--(MMULT(--(ISNUMBER(SEARCH(LookupCodes,E2:E18))),ROW(INDIREC T(N2))^0)0)))-- Enter this array formula** in N3. This will extract the dates- that-- meet- the criteria. - - =IF(N2="","",IF(ROWS(N$3:N3)O$2,"",INDEX(D:D,SMAL L(IF(MMULT(--(ISNUMBER(SEARCH(LookupCodes,E$2:E$18))),ROW(INDIR ECT(N$2))^0)0,ROW(E$2:E$18)),ROWS(N$3:N3)))))-- Format as Date. Copy down until you get blanks. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key- and-- the- SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "tornado1981" wrote in- message-- - ...- A B 1 Name Code 2 London 301 3 Paris 302 4 Rome 303 5 Moscow 304 6 Madrid 305 7 Berlin 306 9 Name Code 10 Brazilia 307 11 Buenos Aires 308 12 Santiago 309 13 Montevide 310 14 Quito 311 15 Lima 312 D E 1 Date Code 2 17/3/2010 301 3 22/3/2010 306 3 23/6/2010 312 4 14/5/2010 305 5 22/9/2010 302+301 6 25/10/2010 308 7 1/3/2010 311 8 9/11/2010 301 9 6/5/2010 307 10 17/7/2010 302+305 11 28/7/2010 304 12 6/8/2010 309 13 31/12/2010 310 14 2/2/2010 301 15 9/2/2010 303 16 5/6/2010 307 17 1/10/2010 307+312 What i want to do is that when i enter a code (let's say 301)- in-- the- cell G2, then in cells O3:O15 appear the dates corresponding to- the- codes 300,301,302,303,304,305 & 306. And if i enter 312, then in cells O3:O15 appear the dates- corresponding- to the codes 307,308,309,310,311 & 312 ( taking into account- that-- "301"- that i entered in cell G2 is a part of "302+301" in cell E6) I entered 3 formulas that have solved a part of the problem,- but-- the- last problem is that the date corresponding to "302+301" was not included coz it's not exactly what I entered in G2.. So could u- please- modify my formulas or create others to solve that problem ?? Here are my formulas In O1 - - =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},MATCH(G 2,CodeListA,0),MATCH(G2,CodeListB,0)),CHOOSE({1,2}-- ,"CodeListA","CodeListB")) In O2 (ctrl+shift+enter) {=SUM(IF(ISNUMBER(MATCH(E2:E18,INDIRECT(O1),0)),1) )} In Range O3:O15 (ctrl+shift+enter) - - {=IF(ISNA($O$1),"",IF(ROWS($O$3:O3)<=$O$2,INDEX($D $2:$D$18,SMALL(IF(ISNUMBER(MATCH($E$2:$E$18,INDIRE-- CT($O$1),0)),ROW($D$2:$D$18)-ROW($D$2)+1),ROWS($O$3:O3))),""))} And these are the results N O 1 Range : CodeListA Where CodeListA is the range B2:B7 2 No. of dates 7 While it has to be 9 !! 3 Dates: 17/3/2010 22/3/2010 14/5/2010 9/11/2010 28/7/2010 2/2/2010 9/2/2010 Thank you -- tornado1981- --- Thanks so much valko for ur help .. but would u please attach an- excel- sheet for explanation ? i got some errors when i apply ur codes Thank u -- tornado1981 --- -- tornado1981 - -- tornado1981 |
Yeah .. sorry i forgot to highlight it :)
Quote:
|
Multiple table lookup
This is getting really complicated. I hope there aren't any more changes!
The conditional formatting will work as long as there aren't duplicate dates where one date is related to N2 and a duplicate date is not related to N2. http://cjoint.com/?ekfSwElmbY -- Biff Microsoft Excel MVP "tornado1981" wrote in message ... Yeah .. sorry i forgot to highlight it :) T. Valko;943895 Wrote: Ok, you say you want to highlight the dates associated with N2. Why isn't 10/25/2010 (or 25/10/2010) highlighted? -- Biff Microsoft Excel MVP "tornado1981" wrote in message ...- Hi Valko Would u please take a look at that file please Thank u http://cjoint.com/?ehuoyrWg8V T. Valko;943145 Wrote:- Tornado1981(2).xls http://cjoint.com/?egfrN0oCsQ -- Biff Microsoft Excel MVP "tornado1981" wrote in message ...-- Thanks so much T. Valko .. That's really awesome .. But could we- modify- these codes to search in columns E & F instead of Just Column E ?? the sheet would be like that http://cjoint.com/data/efv6hthN8f.htm T. Valko;943026 Wrote:- Here's a small sample file that demonstrates this.. Tornado1981.xls 17kb http://cjoint.com/?efuMzriA6o -- Biff Microsoft Excel MVP "tornado1981" wrote in- message- ...-- T. Valko;942822 Wrote:- Tweak... -- =IF(N2="","",IF(ROWS(N$3:N3)O$2,"",...-- Make N2 row absolute. =IF(N$2="","",IF(ROWS(N$3:N3)O$2,"",... -- Biff Microsoft Excel MVP "T. Valko" wrote in message ...-- On the surface this would seem like a fairly straightforward- lookup/data- extraction but in reality it's a bit complex! What makes it- complex-- are- the cells where there are multiple codes: - 302+301 302+305 307+312- CodeListA refers to B$2:B$7 CodeListB refers to B$10:B$15 Create this additional name: LookupCodes Refers to: =TRANSPOSE(INDIRECT(Sheet1!$N$2)) Use the appropriate sheet name. G2 = some code like 301 Enter this formula in N2. This will return the name of the- range-- that-- the- code number in G2 is located in. - - - =IF(COUNTIF(CodeListA,G2),"CodeListA",IF(COUNTIF(C odeListB,G2),"CodeListB",""))--- Enter this array formula** in O2. This will return the count- of-- records- that meet the criteria. - - - =IF(N2="","",SUM(--(MMULT(--(ISNUMBER(SEARCH(LookupCodes,E2:E18))),ROW(INDIREC T(N2))^0)0)))--- Enter this array formula** in N3. This will extract the dates- that-- meet- the criteria. - - - =IF(N2="","",IF(ROWS(N$3:N3)O$2,"",INDEX(D:D,SMAL L(IF(MMULT(--(ISNUMBER(SEARCH(LookupCodes,E$2:E$18))),ROW(INDIR ECT(N$2))^0)0,ROW(E$2:E$18)),ROWS(N$3:N3)))))--- Format as Date. Copy down until you get blanks. ** array formulas need to be entered using the key combination- of- CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL- key-- and-- the- SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "tornado1981" wrote in- message-- - ...- A B 1 Name Code 2 London 301 3 Paris 302 4 Rome 303 5 Moscow 304 6 Madrid 305 7 Berlin 306 9 Name Code 10 Brazilia 307 11 Buenos Aires 308 12 Santiago 309 13 Montevide 310 14 Quito 311 15 Lima 312 D E 1 Date Code 2 17/3/2010 301 3 22/3/2010 306 3 23/6/2010 312 4 14/5/2010 305 5 22/9/2010 302+301 6 25/10/2010 308 7 1/3/2010 311 8 9/11/2010 301 9 6/5/2010 307 10 17/7/2010 302+305 11 28/7/2010 304 12 6/8/2010 309 13 31/12/2010 310 14 2/2/2010 301 15 9/2/2010 303 16 5/6/2010 307 17 1/10/2010 307+312 What i want to do is that when i enter a code (let's say- 301)-- in-- the- cell G2, then in cells O3:O15 appear the dates corresponding- to-- the- codes 300,301,302,303,304,305 & 306. And if i enter 312, then in cells O3:O15 appear the dates- corresponding- to the codes 307,308,309,310,311 & 312 ( taking into account- that-- "301"- that i entered in cell G2 is a part of "302+301" in cell E6) I entered 3 formulas that have solved a part of the problem,- but-- the- last problem is that the date corresponding to "302+301" was- not- included coz it's not exactly what I entered in G2.. So could- u-- please- modify my formulas or create others to solve that problem ?? Here are my formulas In O1 - - - =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},MATCH(G 2,CodeListA,0),MATCH(G2,CodeListB,0)),CHOOSE({1,2}--- ,"CodeListA","CodeListB")) In O2 (ctrl+shift+enter) {=SUM(IF(ISNUMBER(MATCH(E2:E18,INDIRECT(O1),0)),1) )} In Range O3:O15 (ctrl+shift+enter) - - - {=IF(ISNA($O$1),"",IF(ROWS($O$3:O3)<=$O$2,INDEX($D $2:$D$18,SMALL(IF(ISNUMBER(MATCH($E$2:$E$18,INDIRE--- - CT($O$1),0)),ROW($D$2:$D$18)-ROW($D$2)+1),ROWS($O$3:O3))),""))}- And these are the results N O 1 Range : CodeListA Where CodeListA is the range B2:B7 2 No. of dates 7 While it has to be 9 !! 3 Dates: 17/3/2010 22/3/2010 14/5/2010 9/11/2010 28/7/2010 2/2/2010 9/2/2010 Thank you -- tornado1981- --- Thanks so much valko for ur help .. but would u please attach an- excel- sheet for explanation ? i got some errors when i apply ur codes Thank u -- tornado1981 --- -- tornado1981 --- -- tornado1981 - -- tornado1981 |
| All times are GMT +1. The time now is 05:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com