+ Reply to Thread
Results 1 to 7 of 7

Index/Match to Create a Grid

  1. #1
    Registered User
    Join Date
    11-09-2015
    Location
    Florida
    MS-Off Ver
    O365
    Posts
    61

    Index/Match to Create a Grid

    Guess it my week for questions. :) Trying to figure out how to create a grid with "x" indicating a match of names and dates. It's a duty assignment schedule for clarification. The attached has a sample, but I've been playing with

    =INDEX($E$1, MATCH(1, ($J3=$F$3:$F$18)*($L$2=$G$3:$G$18),0))

    It seems to work in the first cell, but then when a name repeats I get a Ref# error. Other variations give me the N/A error. Looking for a nudge in the right direction and as always I appreciate the help in learning how to do new things!

    WHH3
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    490

    Re: Index/Match to Create a Grid

    Try this: =IF(SUMPRODUCT(($E2=Table1[Date])*(F$1=Table1[Dir])),"x",)

  3. #3
    Registered User
    Join Date
    11-09-2015
    Location
    Florida
    MS-Off Ver
    O365
    Posts
    61

    Re: Index/Match to Create a Grid

    That seems to work in the sample data, except that it appears to skip ever other row. I pasted in the first cell and dragged the formula if that matters. Going to tinker with it and hopefully figure out why; confirm cells, etc. Screen shot attached and THANKS!!!!
    Attached Images Attached Images

  4. #4
    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,147

    Re: Index/Match to Create a Grid

    Put the formula in F2 then COPY to other row 2 entries: dragging will change the formula (which is why I dislike tables!!)

    =IF(SUMPRODUCT(($E2=Table1[[Date]:[Date]])*(F$1=Table1[[Dir]:[Dir]])),"x","")

    can be dragged
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  5. #5
    Registered User
    Join Date
    11-09-2015
    Location
    Florida
    MS-Off Ver
    O365
    Posts
    61

    Re: Index/Match to Create a Grid

    Wow, thanks John. I wasn't aware there was a difference in dragging (other than references changing like n2 to n3) and using the copy/paste function. Thanks for education!
    Also, thanks to Raul for getting me started down the right path!

    Whh3 - Solved!

  6. #6
    Valued Forum Contributor
    Join Date
    02-06-2013
    Location
    Germany
    MS-Off Ver
    365
    Posts
    490

    Re: Index/Match to Create a Grid

    Yea, if you fill the formula with Ctrl+Enter there should not be a problem.

    I was not even aware that dragging changes the formula...what the...
    But then again, for over 12 yrs. now, I am avoiding Tables in Excel altogether as I find it's behaviour beyond very basic stuff completely random and illogical at times xD

  7. #7
    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,147

    Re: Index/Match to Create a Grid

    I am not a tables fan either! but do recognise SOME of the benefits they provide.

+ 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: 1
    Last Post: 07-14-2021, 12:11 PM
  2. need help using index/match to search grid
    By tsiguy96 in forum Excel General
    Replies: 2
    Last Post: 12-11-2013, 08:33 AM
  3. Using Index and Match to lookup a value in a grid
    By jcranst in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-05-2012, 01:01 PM
  4. Price Grid [index/lookup/match]
    By casey63 in forum Excel General
    Replies: 5
    Last Post: 04-01-2012, 05:26 PM
  5. Price grid (index/lookup/match)
    By casey63 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-01-2012, 05:23 PM
  6. Price Grid [Index/Lookup/Match]
    By jaredg in forum Excel General
    Replies: 8
    Last Post: 01-25-2012, 01:27 PM
  7. Staffing Grid - Index Match Help
    By Justinmih in forum Excel General
    Replies: 0
    Last Post: 06-21-2010, 07:27 PM

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