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,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


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,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

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,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