Closed Thread
Results 1 to 4 of 4

Multiple tables lookup

  1. #1
    Registered User
    Join Date
    04-04-2010
    Location
    Egypt
    MS-Off Ver
    Excel 2007
    Posts
    7

    Multiple tables 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(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

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Multiple tables lookup

    A sample file would help I suspect but perhaps:

    Please Login or Register  to view this content.
    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 ?
    Last edited by DonkeyOte; 04-04-2010 at 08:45 AM.

  3. #3
    Registered User
    Join Date
    04-04-2010
    Location
    Egypt
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Multiple tables lookup

    yes it is unique

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Multiple tables lookup

    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

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1