+ Reply to Thread
Results 1 to 10 of 10

Index and match problem

  1. 04-03-2016, 04:23 PM #1
    MANISH2AGRAWAL
    MANISH2AGRAWAL is offline
    Registered User
    Join Date
    04-01-2016
    Location
    india
    MS-Off Ver
    7
    Posts
    17

    Index and match problem

    Suppose column a1 to a6 has different dates and column b1 to b6 has same text as "date".

    Drive largest date from column a using index and match formula,
    Reply With QuoteRegister To Reply
  2. 04-03-2016, 05:16 PM #2
    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: Index and match problem

    Hi,

    Why do you mention b1:b6 if you're only interested in column A? And why do you need an Index and Match just to find the highest number (date) from column A when =MAX(A1:A6) will do just that?
    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
  3. 04-03-2016, 10:39 PM #3
    MANISH2AGRAWAL
    MANISH2AGRAWAL is offline
    Registered User
    Join Date
    04-01-2016
    Location
    india
    MS-Off Ver
    7
    Posts
    17
    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Why do you mention b1:b6 if you're only interested in column A? And why do you need an Index and Match just to find the highest number (date) from column A when =MAX(A1:A6) will do just that?
    B1:b6 contain text as "satteled". In my worksheet if column b is = satteled, then only next step can be performed.
    Reply With QuoteRegister To Reply
  4. 04-03-2016, 11:31 PM #4
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Index and match problem

    Maybe you are taking about maxif!?
    something like this by DDL
    =SUMPRODUCT(MAX((B1:B6="satteled")*A1:A6))
    or by Tigeravatar
    =Max(Index((B1:B6="satteled")*A1:A6,))
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  5. 04-04-2016, 01:59 AM #5
    MANISH2AGRAWAL
    MANISH2AGRAWAL is offline
    Registered User
    Join Date
    04-01-2016
    Location
    india
    MS-Off Ver
    7
    Posts
    17

    Re: Index and match problem

    Attached is the sheet wherein i want column j to calculate overdue days, based on payment received date - invoice date, if column i is "not satteled" & payment received date - next invoice date , if column i is "satteled".
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 04-04-2016, 03:37 AM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,722

    Re: Index and match problem

    Could you upload another workbook with desired results hand-typed in?
    Dave
    Reply With QuoteRegister To Reply
  7. 04-04-2016, 03:59 AM #7
    MANISH2AGRAWAL
    MANISH2AGRAWAL is offline
    Registered User
    Join Date
    04-01-2016
    Location
    india
    MS-Off Ver
    7
    Posts
    17

    Re: Index and match problem

    Please find attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 04-04-2016, 07:08 AM #8
    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: Index and match problem

    Sorry but even with your latest workbook I still don't understand your calculations. Please explain the calculation behind your Expected column K numbers by reference to the cells you have used.

    I'm also confused by your reference to a 'Next invoice date' and which is the base date used for overdue. I'm assuming it's today's date but this isn't clear.

    Incidentally you should change the spelling to 'Settled' and 'Not Settled'
    Reply With QuoteRegister To Reply
  9. 04-04-2016, 08:21 AM #9
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,941

    Re: Index and match problem

    J2 
    Please Login or Register  to view this content.
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  10. 04-08-2016, 02:44 AM #10
    MANISH2AGRAWAL
    MANISH2AGRAWAL is offline
    Registered User
    Join Date
    04-01-2016
    Location
    india
    MS-Off Ver
    7
    Posts
    17

    Re: Index and match problem

    thanks siva
    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. Formatting with INDEX MATCH MATCH Problem
    By SwissExcel in forum Excel General
    Replies: 1
    Last Post: 07-22-2015, 07:40 AM
  2. [SOLVED] Index Match Match - syntax problem
    By Dabooka in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2014, 09:05 AM
  3. Index / Match / Vlookup type problem - £20 to person who solves this! X ref problem
    By aceparts in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-28-2013, 05:08 PM
  4. Problem with INDEX, INDIRECT, MATCH, MATCH
    By JO505 in forum Excel General
    Replies: 5
    Last Post: 09-01-2011, 05:51 PM
  5. Possible Index, Match, Max problem
    By penfold in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-17-2011, 11:35 AM
  6. Problem with IF(INDEX,(Match))
    By Jogier505 in forum Excel General
    Replies: 4
    Last Post: 07-07-2010, 04:02 PM
  7. Index match problem getting #N/A
    By mugs62 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-27-2009, 05:18 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