+ Reply to Thread
Results 1 to 9 of 9

Index Match? - Find value in given row then return diff value in same row and diff column

  1. 02-17-2021, 08:38 AM #1
    jfried11
    jfried11 is offline
    Registered User
    Join Date
    02-07-2014
    Location
    Berlin, Germany
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    7

    Index Match? - Find value in given row then return diff value in same row and diff column

    Hi all,

    Banging my head against the wall on this one:

    I have a budget which I'm trying to build a KPI sheet into. What I would like to do is build formulas that automatically check against today's date, then return assorted KPIs based on the month, quarter, year.

    I'm struggling to do this dynamically. I have a row in my budget sheet of date codes, and then today's date codes in my KPI sheet. Ideally, in my KPI sheet, I would have equations that did the following:

    Check today's date against the values in the corresponding row in my budget sheet, and then when there is a match return a value from the same column but a different row.

    I know this is an index match function, but I'm banging my head against the wall. Sample worksheet attached with two examples. Can anyone help?

    Thanks!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-17-2021, 08:47 AM #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 2403
    Posts
    44,023

    Re: Index Match? - Find value in given row then return diff value in same row and diff col

    The whole thing begins to fall down in C9 & D9.

    1. Why is C9 the 15 th of June and not the first? The dates generated by your formula are going to be date/times AND all over the place and will need to be replaced. But also...

    2. Since today is 17 Feb, why is "last month" December 2020?
    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.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 02-17-2021, 08:59 AM #3
    jfried11
    jfried11 is offline
    Registered User
    Join Date
    02-07-2014
    Location
    Berlin, Germany
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    7

    Re: Index Match? - Find value in given row then return diff value in same row and diff col

    Hey Glenn,

    1. Starting on the 15th allows me to use "=C9+365.25/12" as my equation to add months, which I find easier than other methods.
    2. This is just for explanatory/display purposes- you are right that it should be Jan.

    Have updated the explantory doc and reattched it.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 02-17-2021, 09:02 AM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,776

    Re: Index Match? - Find value in given row then return diff value in same row and diff col

    Is there any reason why you don't use the EDATE function to add months???
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  5. 02-17-2021, 09:02 AM #5
    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 2403
    Posts
    44,023

    Re: Index Match? - Find value in given row then return diff value in same row and diff col

    OK. No reply, To get things moving, I have changed C9 to 1st June and D9 to

    =EDATE(C9,1)

    copied across. Now the dayts are JUST that. 1st of each month, with NO time component as well.

    a bit confused about the month you want calculated, so for now, I put a date in KPI C1. To return the expened for that date:

    =INDEX('Budget v. Actuals'!$36:$36,,MATCH(KPIs!$C$1,'Budget v. Actuals'!$9:$9,0))

    and to return the sum that and the preceeding 2 months of Oct-Nov-Dec:


    =SUM(INDEX('Budget v. Actuals'!$36:$36,,MATCH(KPIs!$C$1,'Budget v. Actuals'!$9:$9,0)+{0,-1,-2}))

    in Germany you may need ; instead of ,
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 02-17-2021, 09:03 AM #6
    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 2403
    Posts
    44,023

    Re: Index Match? - Find value in given row then return diff value in same row and diff col

    i see you have now replied!!
    Reply With QuoteRegister To Reply
  7. 02-17-2021, 09:07 AM #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 2403
    Posts
    44,023

    Re: Index Match? - Find value in given row then return diff value in same row and diff col

    I have modified the formulae to use TODAY() and, of course, they now give results for Jan and Nov-Jan
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 02-17-2021, 09:17 AM #8
    jfried11
    jfried11 is offline
    Registered User
    Join Date
    02-07-2014
    Location
    Berlin, Germany
    MS-Off Ver
    Microsoft Excel for Mac
    Posts
    7

    Re: Index Match? - Find value in given row then return diff value in same row and diff col

    Thanks all!

    Love to learn more about to properly use index match and, love the added bonus of learning correct month etiquette Am marking this as solved.
    Reply With QuoteRegister To Reply
  9. 02-17-2021, 09:18 AM #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 2403
    Posts
    44,023

    Re: Index Match? - Find value in given row then return diff value in same row and diff col

    You're welcome.


    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
    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. Offset Match? Find something in 1st row, search in column, sum product diff column
    By mecexcel in forum Excel General
    Replies: 5
    Last Post: 10-23-2015, 12:35 PM
  2. [SOLVED] If find a match then add two diff cells on same row
    By butair in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-19-2014, 10:11 AM
  3. Pull rev letters from index(match), multiple rows of same DWG# each diff rev
    By Cwyso1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-28-2014, 03:03 PM
  4. Search 2 diff sheets with diff column headers and identify common names, return entire row
    By justin11 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-30-2013, 07:42 PM
  5. Diff. between Vlookup and Match/Index
    By RATUL in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2013, 02:03 PM
  6. Scan column for text and copy specific range thenpaste into diff column on diff sheet
    By the duke in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2013, 06:50 AM
  7. [SOLVED] Index? Match? Function to sort and return value fr diff column in
    By Smurfette in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-27-2006, 12:50 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