+ Reply to Thread
Results 1 to 4 of 4

Index ctrl-shift entered formula not giving the results I was hoping for

  1. #1
    Registered User
    Join Date
    05-17-2020
    Location
    Heiloo, Nederland
    MS-Off Ver
    2007
    Posts
    28

    Index ctrl-shift entered formula not giving the results I was hoping for

    Good day everybody, again...
    Yeah, I'm having another question and it is (surprisingly) about the same database every other question so far I have asked about...
    Well, what I am facing now is that I am using one of my entrusted formulae (cell H2, in this case I made it a text-entered value) is not delivering the results I hoped for (the results as shown in column F).
    So I am looking for the right (probably ctrl-shift entered) formula that gives these results.

    Note that the list with numbers in column C and E is varying all the time. That means that the list in column E and F is ever changing, growing or shifting. So the formula has to be able to cover that without making any mistakes (I want to use this column E for something else, in another tab).

    So see attachment and already thanks in advance for thinking along.
    Attached Files Attached Files
    Thank you for your helpful answer.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,216

    Re: Index ctrl-shift entered formula not giving the results I was hoping for

    Note that the list with numbers in column C and E is varying all the time. That means that the list in column E and F is ever changing, growing or shifting. So the formula has to be able to cover that without making any mistakes
    That said I would recommend setting up some dynamic named ranges (DNR) in Name Manager because:
    1. they dynamically adjust in size to fit the data
    2. reduce the need to edit formulas with new data
    3. make formulas much easier to write, follow, edit and trouble-shoot

    You will find these in the attached Name Manager. This upload will adjust for regional settings replacing the "," argument separators with ";". (I don't trust my typing skills enough to edit the formulas accurately in this post.)

    Even though, for others watching this thread here is the list of DNRs (without regional adjustments):

    Note all of these are defined with reference to the size of column A (Code)

    Code
    =Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH("zzzzz",Sheet1!$A:$A))
    Country
    =Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH("zzzzz",Sheet1!$A:$A))
    Index_Match
    =Sheet1!$E$2:INDEX(Sheet1!$E:$E,MATCH("zzzzz",Sheet1!$A:$A))
    Total
    =Sheet1!$C$2:INDEX(Sheet1!$C:$C,MATCH("zzzzz",Sheet1!$A:$A))


    Then (CSE) the formula in F2 filled down (I did it in G2 to demonstrate) is
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Also given the shifting of data you mention I recommend this change to your formula in E2 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in the event the data shifts rows and the ROW() function changes the ROWS($E$2:$E2) will maintain without need for editing.
    Dave

  3. #3
    Registered User
    Join Date
    05-17-2020
    Location
    Heiloo, Nederland
    MS-Off Ver
    2007
    Posts
    28

    Re: Index ctrl-shift entered formula not giving the results I was hoping for

    Yeah, that's doing the trick. Thanks!
    I'm going not only to implement the formula but also study it. As I always do.
    Thanks a lot, I'm happy with it.

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

    Re: Index ctrl-shift entered formula not giving the results I was hoping for

    @Daniel - are you using Excel 2007 or something much newer? If the latter, please update your profile (for example, 2007 may in your case be a release number for MS365).
    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.

+ 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. Replies: 4
    Last Post: 04-27-2021, 11:49 AM
  2. [SOLVED] Index/match giving correct results, wrong results & #N/A results...sometimes
    By mrteater in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-27-2019, 10:41 AM
  3. [SOLVED] lookup formula not giving expected results when criteria entered
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-31-2019, 06:10 AM
  4. Replies: 15
    Last Post: 01-03-2018, 03:32 PM
  5. [SOLVED] Rank countif with index match is not giving top results
    By amartin575 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-01-2014, 10:17 PM
  6. [SOLVED] Formula For CTRL+SHIFT+;
    By donscarwash in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-11-2005, 09:05 AM
  7. How to show array results without {Ctrl+Shift+Enter}
    By dkkchan in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-22-2005, 07:39 AM

Tags for this Thread

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