Multiple tables lookup

**tornado1981** · 04-04-2010, 07:26 AM

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(G2,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,INDIRECT($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

**DonkeyOte** · 04-04-2010, 08:40 AM

A sample file would help I suspect but perhaps:

O2: 
=SUMPRODUCT(--ISNUMBER(SEARCH("+"&TRANSPOSE(INDIRECT($P$1))&"+","+"&$E$2:$E$18&"+")))

O3:
=IF(ISNA($O$1),"",IF(ROWS($O$3:O3)<=$O$2,INDEX($D$2:$D$18,SMALL(IF(ISNUMBER(SEARCH("+"&TRANSPOSE(INDIRECT($P$1))&"+","+"&$E$2:$E$18&"+")),ROW($D$2:$D$18)-ROW($D$2)+1),ROWS($O$3:O3))),""))

both of the above to be confirmed with CTRL + SHIFT + ENTER (O3 then copied down)

EDIT:

in retrospect the above might not be quite what you're after as in the present form 22nd Sep would be listed twice in the results given both 301 & 302 appear under this value... are the date values unique ?

**tornado1981** · 04-04-2010, 10:07 AM

yes it is unique

**Paul** · 04-04-2010, 10:46 AM

Tornado,

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

http://www.mrexcel.com/forum/showthread.php?t=459448