+ Reply to Thread
Results 1 to 10 of 10

Index and Match help.

  1. #1
    Registered User
    Join Date
    06-05-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    55

    Index and Match help.

    I would like a list of items to appear in sheet 3 b2:b31, that match sheet 3 d41. The data needs to come from the table in sheet 2 d93:ac136. In summary I would like all of the items in sheet 2 d94 d136 to be listed without spaces in sheet 3 b2:b31. Based on the date. I have tried several index and match formulations without success.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Index and Match help.

    The dates in 2 (row 93) and 3 (row 41) are different so how are they meant to match?

  3. #3
    Registered User
    Join Date
    06-05-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Index and Match help.

    It is so difficult to explain this in text. I am trying to get all of the items under the date 1/2/2016 in sheet 2 d93:d136 to be listed in sheet 3 b2:b32 based on the date entered in sheet 3 d41. That date will fluctuate. I am trying to look in the entire range sheet 2 d93:ac136. There may be a better way. I just need there to be no spaces. I know I can use an H lookup and get the same result but with spaces. Here is what y H lookup would look like in sheet 3 b2. Maybe this helps. =HLOOKUP($D$41,'2'!D93:$AC$136,2,0)

  4. #4
    Valued Forum Contributor Gatti's Avatar
    Join Date
    06-08-2015
    Location
    Brasil, São Paulo, Ribeirão Preto
    MS-Off Ver
    365
    Posts
    346

    Re: Index and Match help.

    Hello,

    What you want to put in AMOUNT and DUE columns (sheet 3)?

    Almost done your formula

  5. #5
    Valued Forum Contributor Gatti's Avatar
    Join Date
    06-08-2015
    Location
    Brasil, São Paulo, Ribeirão Preto
    MS-Off Ver
    365
    Posts
    346

    Re: Index and Match help.

    Sheet 3, cel B2, formula:

    Please Login or Register  to view this content.
    Press CTRL + SHIFT + ENTER to make it matrix

    Edit: Attached file

    expdud5.xlsx
    Last edited by Gatti; 01-04-2016 at 01:00 PM.

  6. #6
    Registered User
    Join Date
    06-05-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Index and Match help.

    Okay. It works for that one column. Can you make it look at the entire range. If I change the date in d41 to lets say 1/16/2016 it will bring in the items under that date which is in the next column in sheet 2. I do not need anything to populate in the amount and the due.

    Thanks
    John

  7. #7
    Valued Forum Contributor Gatti's Avatar
    Join Date
    06-08-2015
    Location
    Brasil, São Paulo, Ribeirão Preto
    MS-Off Ver
    365
    Posts
    346

    Re: Index and Match help.

    The attachment I posted does that... The whole range got the formula

    Is there anything else you need?

  8. #8
    Registered User
    Join Date
    06-05-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Index and Match help.

    Sorry. I must be doing something wrong. If I change the date in d41, it returns nothing.

  9. #9
    Valued Forum Contributor Gatti's Avatar
    Join Date
    06-08-2015
    Location
    Brasil, São Paulo, Ribeirão Preto
    MS-Off Ver
    365
    Posts
    346

    Re: Index and Match help.

    Probably because there isn't this date in Headers range

    02/01/2016 16/01/2016 30/01/2016 13/02/2016 27/02/2016 12/03/2016 26/03/2016 09/04/2016 23/04/2016 07/05/2016 21/05/2016 04/06/2016 18/06/2016 02/07/2016 16/07/2016 30/07/2016 13/08/2016 27/08/2016 10/09/2016 24/09/2016 08/10/2016 22/10/2016 05/11/2016 19/11/2016 03/12/2016 17/12/2016

    These are the avaible dates

  10. #10
    Registered User
    Join Date
    06-05-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Index and Match help.

    My. Mistake. It works so well. Thank you.

+ 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. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  2. INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges
    By Andrew-Mark in forum Excel General
    Replies: 3
    Last Post: 02-27-2015, 10:56 PM
  3. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  4. [SOLVED] Index Match from a cell populated from index match
    By MarcLewis in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-15-2013, 05:30 AM
  5. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  6. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  7. Replies: 5
    Last Post: 02-29-2012, 08:51 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1