Results 1 to 4 of 4

Thread: Lookup value from reversed columns and rows

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Lookup value from reversed columns and rows

    Source table
    tine 2 tine 2 tine 2 Hitte 3 Hitte 3 Hitte 3
    PM assigned WO assigned Task assigned PM assigned WO assigned Task assigned
    18-May 6 1 0 48 0 0
    19-May 6.5 1 0 48 0 96
    20-May 7 1 12 48 48 64
    21-May 7.5 7 12 24 80 2.56
    22-May 4 1 0 64 16 0
    23-May 4.5 1 18 72 16 5.76
    24-May 5 10 20 80 3.2 6.4

    Table to implement lookup

    18-May 19-May 20-May 21-May 22-May 23-May 24-May
    tine 2 PM assigned
    tine 2 WO assigned
    tine 2 Task assigned
    Hitte 3 PM assigned
    Hitte 3 WO assigned
    Hitte 3 Task assigned

    This is only an example of a large database I have with bunch of other formulas. So I cannot change the structure of tables, especially 2nd one.
    I need to find a way to create a look up for 2nd table. Both tables are not static, as new locations can added meaning 1st table can extend in columns, while 2nd table will be extended in rows. When this happens I would like a casual user to have no problem extending the formula for new locations.
    So far I tried Sumproduct and IndexMatch : both didn't work out, however I could make a mistake somewhere. I tried to Concatenate location and assigned, but still didn't get any good result.
    VBA might be a solution, but I would still prefer a formula approach.

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,376
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup value from reversed columns and rows

    Here's one way:

    Excel 2010
    ABCDEFGHIJ
    1
    2tine 2tine 2tine 2Hitte 3Hitte 3Hitte 3
    3PM assignedWO assignedTask assignedPM assignedWO assignedTask assigned
    418-May6104800
    519-May6.51048096
    620-May7112484864
    721-May7.571224802.56
    822-May41064160
    923-May4.511872165.76
    1024-May51020803.26.4
    11
    12
    13
    14
    15
    16
    17
    18
    19
    2018-May19-May20-May21-May22-May23-May24-May
    21tine 2PM assigned66.577.544.55
    22tine 2WO assigned11171110
    23tine 2Task assigned00121201820
    24Hitte 3PM assigned48484824647280
    25Hitte 3WO assigned00488016163.2
    26Hitte 3Task assigned096642.5605.766.4

    Sheet1



    Array Formulas
    CellFormula
    D21{=INDEX($C$4:$H$10,MATCH(D$20,$B$4:$B$10,0), MATCH(1,($C$2:$H$2=$B21)*($C$3:$H$3=$C21),0))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Last edited by Comfy; May 30th, 2019 at 10:54 AM.

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup value from reversed columns and rows

    Thank you a lot.
    That's what I have been looking for.

  4. #4
    New Member
    Join Date
    Jun 2018
    Posts
    46
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup value from reversed columns and rows

    Is it possible to create formula that would sum values by several dates for each location and assigned in the same case ?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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