+ Reply to Thread
Results 1 to 14 of 14

Lookup that looks down then across (dynamic rows)

  1. #1
    Registered User
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Excel 2016
    Posts
    85

    Lookup that looks down then across (dynamic rows)

    Hi all,

    Please could someone help with a formula that would Lookup a value then looks down a table to find it then across (dynamic rows). The rows will retract and expand depending on the date/day of the month.

    For ease I have attached a sample worksheet with some representative sample data and some manually calculated results. The worksheet is GDPR checked and there is no sensitive data on there.

    Thank you for your help and as always very much appreciated.

    Mark
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,490

    Re: Lookup that looks down then across (dynamic rows)

    Are you still using Excel 2010, Mark? If not, please update your forum profile. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,490

    Re: Lookup that looks down then across (dynamic rows)

    Are you open to suggestion regarding the layout of your data? This could be very easy with a couple of tweaks ...

  4. #4
    Registered User
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Excel 2016
    Posts
    85

    Re: Lookup that looks down then across (dynamic rows)

    Thanks Ali for the prompt and I have updated my profile, never realised I had been a member for so long.

    As for the other question then the answer is no I'm afraid - the original data is a direct dump from a WFM system that is notorious for its non-Excel friendly reporting. Normally I would use a Microsoft SQL report builder to make a more Excel friendly version but unable to do with this particular report. It's basically how the report dumps out, not great I know hence why its beyond my abilities.
    Last edited by MarkoGreenio; 08-28-2020 at 10:09 AM.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,490

    Re: Lookup that looks down then across (dynamic rows)

    That's a poor answer.

    Did you know that you do not need to be bound by data dump layouts? PowerQuery is your friend and can reshape the data for you to make what you want to do with it much easier.

  6. #6
    Registered User
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Excel 2016
    Posts
    85

    Re: Lookup that looks down then across (dynamic rows)

    Thanks Ali, I will take a look at some online videos and have a look wish me luck!!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,490

    Re: Lookup that looks down then across (dynamic rows)

    I can help if you get stuck.

  8. #8
    Registered User
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Excel 2016
    Posts
    85

    Re: Lookup that looks down then across (dynamic rows)

    Thank you that is very much appreciated have a great weekend in the meantime and enjoy the Bank Holiday, hope the weather is better for you down there than it is in Yorkshire.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,490

    Re: Lookup that looks down then across (dynamic rows)

    It's mixed here at the moment - I want to get out and stain the garden furniture this weekend!!!

  10. #10
    Registered User
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Excel 2016
    Posts
    85

    Re: Lookup that looks down then across (dynamic rows)

    Hi Ali, hope you had a good weekend, I did indeed try your suggestion but PQ is probably something I need to invest time in. As a quick fix do you suggest I close down this thread and repost?

    I have sourced a few courses off the internet and we have resource within the company so hopefully I will be ok after a few hours training.

    Thank you

    Mark

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,490

    Re: Lookup that looks down then across (dynamic rows)

    No, you can't have duplicate threads. If you want a different solution, then just bumping this thread (which you've done) and saying so is fine. Someone else may have a suggestion for you.

  12. #12
    Registered User
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Excel 2016
    Posts
    85

    Re: Lookup that looks down then across (dynamic rows)

    Thanks Ali, appreciate your help.

    I have just added the original text below.

    Please could someone help with a formula that would Lookup a value then looks down a table to find it then across (dynamic rows). The rows will retract and expand depending on the date/day of the month.

    For ease I have attached a sample worksheet with some representative sample data and some manually calculated results. The worksheet is GDPR checked and there is no sensitive data on there.

    Thank you for your help and as always very much appreciated.

    Mark

  13. #13
    Valued Forum Contributor wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    2007/MSO365
    Posts
    857

    Re: Lookup that looks down then across (dynamic rows)

    In response to question #1, do you want to apply the yellow area and get the result? If yes

    All are array formulas, copy and paste down

    HTML Code: 
    P9 cell {=INDEX(A:A,SMALL(IF(($A$9:$C$300<>"Totals:")*ISTEXT($A$9:$C$300),ROW($9:$300),4^8),ROW(1:1)))&""}
    S9 cell {=ROUND(INDEX(I:I,SMALL(IF($A$9:$C$300="Totals:",ROW($9:$300),4^8),ROW(1:1)))&"",2)}

  14. #14
    Registered User
    Join Date
    06-18-2013
    Location
    Barnsley
    MS-Off Ver
    Excel 2016
    Posts
    85

    Re: Lookup that looks down then across (dynamic rows)

    Thank you very much, that formula works perfectly well, I appreciate your help wk9128

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Dynamic Arrays, Dynamic Drop down list, Filters and multiple rows
    By Big_Kev007 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2020, 06:35 AM
  2. [SOLVED] adjusting vba code to make it dynamic as rows data rows differ
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-26-2018, 10:37 PM
  3. [SOLVED] Clear dynamic named sheet, compare cells from 2 sheets, copy all matched rows to dynamic s
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-15-2015, 12:31 PM
  4. [SOLVED] 2 Way Lookup with a dynamic row
    By greenmonke in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-21-2014, 05:17 PM
  5. Replies: 3
    Last Post: 04-08-2014, 03:11 AM
  6. Lookup from fixed rows and dynamic columns
    By Alexvs in forum Excel General
    Replies: 2
    Last Post: 11-09-2010, 05:39 AM
  7. Replies: 2
    Last Post: 05-29-2010, 03:28 AM

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