+ Reply to Thread
Results 1 to 7 of 7

Use Index Match or lookup to create array from array

  1. #1
    Registered User
    Join Date
    08-01-2018
    Location
    Riverside, California
    MS-Off Ver
    365
    Posts
    4

    Use Index Match or lookup to create array from array

    Hello! This is my first post so I haven't figured out how to do like, formatting. so bear with me.

    I am trying to create a somewhat complex gradebook for use in automating some course grade calculations for my students. I am running into a hiccup at a stage in which I have codes to designate whether assignments or participation receive full, partial, or no credit (e.g. a late assignment receives 50% of the points they would have received if it had been on time.

    It's a trivial exercise to create an array of the codes I will input; Where I am struggling is with creating the array of the desired values from the codes. I'm down to use the name manager if that will simplify things.

    Also, because the prof is a strange fellow, I need this to be semi automatic with codes-> values, ie. if I end up adding more codes (late by an hour is 90% for example) that I don't need to go though and manually enter new criteria, but that I can just change the range of my code/value table.

    In bad forum text, because I haven't figured out tables, I have a sampling of what I have and want (manually entered).

    [Participation]
    Code Value
    P 1.0
    T 0.5
    U 0.0

    Table 1 Arrays Codes Desired Array Values
    1 2 3 4 5
    Student1 P P P P P {P,P,P,P,P} {1.0,1.0,1.0,1.0,1.0}
    Student2 U P T P P {U,P,T,P,P} {0.0,1.0,0.5,1.0,1.0}
    Student3 P P P P P {P,P,P,P,P} {1.0,1.0,1.0,1.0,1.0}
    Student4 P T P T P {P,T,P,T,P} {1.0,0.5,1.0,0.5,1.0}
    Student5 P T P P P {P,T,P,P,P} {1.0,0.5,1.0,1.0,1.0}

    What I've concocted so far is

    =INDEX(Participation,MATCH(C11,Participation[Code],0),MATCH("Value",Participation[#Headers],0))

    Which works well to catch individual cells, but I don't know how to get something that I can use on the array to produce an array of values in a single expression or cell (Which will need to be multiplied by points possible array, so yes I believe I need an array of values) Of Note here, I believe I would like a 1D array of values for each student to make the later sumproduct easier.

    Anyway, any assistance is GREATLY appreciated!!
    Attached Files Attached Files
    Last edited by kinemagichemistry; 08-01-2018 at 02:10 PM. Reason: Clarification.

  2. #2
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Use Index Match or lookup to create array from array

    Hi, kinemagichemistry!

    Try this formula:
    =INDEX(Participation[Value],N(IF(1,MATCH($C$11:$G$15,Participation[Code],))))

    Blessings!

  3. #3
    Registered User
    Join Date
    08-01-2018
    Location
    Riverside, California
    MS-Off Ver
    365
    Posts
    4

    Re: Use Index Match or lookup to create array from array

    Hello! If I just straight copy that in, I get a #VALUE error. Stepping through the calculation, It Appears that excel does not like the argument of Match being a 2d array?

  4. #4
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Use Index Match or lookup to create array from array

    Hi again!

    The formula has the entire 2d array into it. If you press F2 (edit mode of formula) and later press F9, you get the array {1,1,1,1,1;0,1,0.5,1,1;1,1,1,1,1;1,0.5,1,0.5,1;1,0.5,1,1,1}
    If you want all the results in the cells, you have to select previously the entire range (5 rows and 5 columns), enter the formula in active cell and press Ctrl + Shift + Enter. Blessings!

  5. #5
    Registered User
    Join Date
    08-01-2018
    Location
    Riverside, California
    MS-Off Ver
    365
    Posts
    4

    Re: Use Index Match or lookup to create array from array

    Hello again! thank you for the clarification, however for my purposes this does not seem to work, as the reason that I need to generate this array is for use in a sumproduct, and this type of array generation does not seem to work (even if I restrict the array to a single line).
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-01-2018
    Location
    Riverside, California
    MS-Off Ver
    365
    Posts
    4

    Re: Use Index Match or lookup to create array from array

    Ah, But if I use it through the Name manager it works like a charm! Thank you very much!

  7. #7
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Use Index Match or lookup to create array from array

    Hi again!

    If I understood, you can use this formula:
    =SUMPRODUCT(SUMIF(Participation[Code],C11:G11,Participation[Value]),C18:G18)

    If not, upload in Excel File your expected results manually and explain better what do you want to achieve. Blessings!

+ 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: 5
    Last Post: 02-22-2018, 01:47 AM
  2. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  3. [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
  4. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  5. Lookup, Index, Match, Array Help
    By Reaye in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-06-2013, 03:36 PM
  6. Replies: 7
    Last Post: 06-19-2011, 12:51 PM
  7. table, index, array, match, lookup?
    By spxer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2006, 04:35 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