+ Reply to Thread
Results 1 to 15 of 15

Challenge with the Index formula

  1. 12-10-2015, 12:38 PM #1
    I.am.Rustam
    I.am.Rustam is offline
    Registered User
    Join Date
    10-21-2015
    Location
    New York
    MS-Off Ver
    MS Office 2013
    Posts
    95

    Challenge with the Index formula

    Hi guys,

    I have some challenges with the attached spreadsheet. I tried Index Match IFs ANDs, but couldn't get what I needed.

    Please help!

    Thanks a lot!
    Attached Files Attached Files
    Last edited by I.am.Rustam; 12-10-2015 at 12:56 PM.
    Reply With QuoteRegister To Reply
  2. 12-10-2015, 01:11 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Challenge with the Index formula

    Nope. I don't follow. Please amend the sheet showing your expected results for a couple of people and explain the difference between Location 1 and location.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  3. 12-10-2015, 01:13 PM #3
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Challenge with the Index formula

    Hi

    Maybe the following although I'm not entirely sure I understand your requirement and if not manually add the results you expect for various permuations so that we may see the end goal.


    As an array formula in C5 (entered with Ctrl Shift Enter) copied down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  4. 12-10-2015, 01:42 PM #4
    I.am.Rustam
    I.am.Rustam is offline
    Registered User
    Join Date
    10-21-2015
    Location
    New York
    MS-Off Ver
    MS Office 2013
    Posts
    95

    Re: Challenge with the Index formula

    Hi,

    Sorry for the confusion. Please see another spreadsheet. I tried to make it clear what I need to get as my final outcome.

    Thanks a lot!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 12-10-2015, 01:53 PM #5
    ImokatExcel
    ImokatExcel is offline
    Registered User
    Join Date
    12-08-2015
    Location
    Ottawa
    MS-Off Ver
    2010
    Posts
    25

    Re: Challenge with the Index formula

    Attached is what you asked for.
    Cheers
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 12-10-2015, 02:01 PM #6
    ImokatExcel
    ImokatExcel is offline
    Registered User
    Join Date
    12-08-2015
    Location
    Ottawa
    MS-Off Ver
    2010
    Posts
    25

    Re: Challenge with the Index formula

    And a slightly more elegant solution:
    =SUMPRODUCT(($E$5:$E$22-$C$5:$C$22>=30)*($B$5:$B$22=$H6)*($D$5:$D$22=I$4)*(($E$5:$E$22-$C$5:$C$22)))
    Paste this formula into that table you provided
    Reply With QuoteRegister To Reply
  7. 12-10-2015, 02:27 PM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Challenge with the Index formula

    That's a nice answer ImokatExcel. I took a slightly different (and maybe overcomplicated) approach, which needed a couple of helper columns (which can be hidden, of course), but which also filled in column H. Messier than yours, but... maybe... complete. we'll see.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 12-10-2015, 02:27 PM #8
    I.am.Rustam
    I.am.Rustam is offline
    Registered User
    Join Date
    10-21-2015
    Location
    New York
    MS-Off Ver
    MS Office 2013
    Posts
    95

    Re: Challenge with the Index formula

    ImokatExcel,

    Your your formulas work perfect and I get what I need ONLY IF I know the H column's names. However, I don't know names in the H columns.... I put them there as an example.

    All I know is the table B5:E22.

    I need to look up names that satisfy the criteria in B5:E22 table, than include the name and corresponding difference for NY, NJ, CT (as you did with your SumIfs formula).

    Please advise if you have an idea!


    Thanks!
    Reply With QuoteRegister To Reply
  9. 12-10-2015, 02:30 PM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Challenge with the Index formula

    I think I've covered the column H aspect. Happy to explain if needed.
    Reply With QuoteRegister To Reply
  10. 12-10-2015, 03:21 PM #10
    I.am.Rustam
    I.am.Rustam is offline
    Registered User
    Join Date
    10-21-2015
    Location
    New York
    MS-Off Ver
    MS Office 2013
    Posts
    95

    Re: Challenge with the Index formula

    Glenn Kennedy,

    Thanks a lot! I appreciate it. Your formulas work perfect! I am getting there! The only one thing I tried to modify myself, but couldn't

    In your formula, the values that are greater than 30 needs to be placed accordingly to the location...

    For example, in the spreadsheet that you attached, Name 1, has the value 35 at location 4, this value needs to go under CT column (which is coded as location 4)

    Is it possible to modify?

    Thanks a lot!!!
    Last edited by I.am.Rustam; 12-10-2015 at 03:28 PM.
    Reply With QuoteRegister To Reply
  11. 12-10-2015, 03:22 PM #11
    ImokatExcel
    ImokatExcel is offline
    Registered User
    Join Date
    12-08-2015
    Location
    Ottawa
    MS-Off Ver
    2010
    Posts
    25

    Re: Challenge with the Index formula

    Im sure I could help you if I understood more clearly what you meant by "You don't have the names". Hopefully Glenn's solution is a more complete one. If you still need help feel free to explain exactly what you need or ask Glenn to explain his analysis.
    Reply With QuoteRegister To Reply
  12. 12-10-2015, 03:26 PM #12
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Challenge with the Index formula

    I am away as from now, for the night. can you illustrate on an Excel sheet what the problems are and I'll take a look first thing tomorrow. If Imokat's formulas for the locations work OK, then just use them in combination with my effort for column H.
    Reply With QuoteRegister To Reply
  13. 12-10-2015, 03:32 PM #13
    I.am.Rustam
    I.am.Rustam is offline
    Registered User
    Join Date
    10-21-2015
    Location
    New York
    MS-Off Ver
    MS Office 2013
    Posts
    95

    Re: Challenge with the Index formula

    Glenn Kennedy,

    Your formulas work perfect! Thanks a lot! I appreciate that!

    The only thing I need to be modified in your formula is that in the spreadsheet that you attached, Name 1, has the value 35 at location 4, this value needs to go under CT column (which is coded as location 4)

    Is it possible? Thanks again!
    Reply With QuoteRegister To Reply
  14. 12-10-2015, 04:43 PM #14
    I.am.Rustam
    I.am.Rustam is offline
    Registered User
    Join Date
    10-21-2015
    Location
    New York
    MS-Off Ver
    MS Office 2013
    Posts
    95

    Re: Challenge with the Index formula

    Glenn and Imokat,

    Thank you guy for contributing your time and effort! I took some part from both of you, combined, and got what I needed!

    Thanks a lot guys!!! Appreciate it.
    Reply With QuoteRegister To Reply
  15. 12-10-2015, 06:21 PM #15
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Challenge with the Index formula

    Great stuff.... Good luck!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. calculating monthly costs in weekly scenario
    By deroo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-08-2014, 07:53 AM
  2. Formula Set Up Challenge
    By Centexcel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2014, 12:05 PM
  3. [SOLVED] Index() Match() V/HLookup??? CHUGE Challenge! Up for it!? Need HELP!
    By BenCrockett in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-14-2013, 05:47 PM
  4. Table index challenge
    By Saturn in forum Excel General
    Replies: 3
    Last Post: 07-06-2012, 09:20 AM
  5. Formula challenge
    By foxguy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2011, 08:20 PM
  6. Formula AVE and IF challenge
    By scomar411 in forum Excel General
    Replies: 0
    Last Post: 07-16-2007, 09:17 PM
  7. I have a formula challenge
    By mgmcdevitt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-22-2005, 02:25 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1