Jump to content


Photo

Is INDEX/MATCH the best solution for this scenario?

INDEX MATCH VLOOKUP

  • Please log in to reply
4 replies to this topic

#1 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 25 November 2014 - 12:58 AM

I've been studying the attached spreadsheet for days. I'm stuck on one of the critical purposes this spreadsheet is meant to address. And while VLOOKUPs may play a small part in the solution, I'm thinking INDEX and MATCH functions will be the primary method of filling in the detail I need on the StudentStatus tab. This is where I'm having trouble getting a grasp on how they're to be implemented.
 
The file is meant to track the classes students complete. This is indicated by the Completion Date column (column CI on the tab DATA). The file will produce the following kinds of reports (among others):
 
  1. Each student has a job title. Each job title has a set of required classes that person has to complete. This can be done with a pivot table, so no issues here. These requirements are defined in the tab RequiredClassConfig.
     
  2. Show what classes each student has completed and what are the remaining classes. This is where I'm finding it a bit more challenging. I plan on having to insert extra columns and formulas to process the data when I receive it, so minimizing my steps would be to my advantage.
Tab defintions:
 
DATA: this is where all the work takes place in determining who has taken what and when. Most of the columns can be considered helper columns, which are mainly used to help me build up to the real formulas to do the work in--what I hope to be--a much more efficient manner. I've successfully used the array on the tab below for Columns D:AO to identify the required classes.
 
Columns AP:CA check to see if a completion date is in column CI. It then looks for the LNCode (column CC), and if it finds the matching code in the header column, it will place a "c" in the cell to show the class is completed.
 
RequiredClassConfig: an array to show what classes are required for each job title (as indicated by an "x"). (thanks to this lesson)
 
LNDecoder: I needed something more manageable to identify the titles of the classes because the titles were way too long (the names in the file have been modified for purposes of examining it hear).
 
So I created this tab to assign codes to each class. It's not used as a source at the moment, but I thought it might come in handy at some point. I use a variation of these codes on the DATA tab in columns AP:CA to show when a class has been completed by the student. This is shown by the "C" at the end of the code. However, this may not be necessary to keep as it was serving me to help formulate a plan.
 
StudentStatus: I envision this to be a source for pivot tables to show the progress for each student, but this is where I'm having trouble merging the pertinent data I'm interested in gathering from the DATA tab and putting an "x" into the corresponding cells to show--in one row per student--what classes they have completed. Something tells me INDEX/MATCH would be perfect for this, but I'm not quite proficient enough to make it work correctly. But in the end my goal is to use this data to create pivot tables that will show what I'm looking to address: completed and incomplete classes for each student.
 
The problem
Ok, so I know what classes they need to take and what classes they've taken. If a student's information were contained in one row, it would be easy, and I wouldn't have to combine that information onto one row for each student. But each row is created when a student takes a class, which means a student's name will show multiple times.
 
Thanks in advance for your assistance.
 
Attached File  sample file2.xlsm   199.58KB   204 downloads


#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 25 November 2014 - 07:38 PM

Hi phoenixrizing,

 

The INDEX and MATCH functions essentially allow you to perform a VLOOKUP function in a more sophisticated way. They can be useful in some situations, but I'm not sure they are will enable you to achieve what you want in this instance.  In most cases it's easier to use VLOOKUP instead of MATCH and INDEX.

 

VLOOKUP is explained in depth in the video lesson: Lesson 3-22 Use a VLOOKUP function for an exact lookup.

 

The real issue here is that you are again trying to create a relational data model within Excel - something for which Excel was never designed.  As you've observed, you need to repeat all of the details for each student in order for the system to work, while a relational database would store the student details in a separate table and join them to the rest of your data using a foreign key.

 

Ideally, this kind of data would be recorded in a true relational database (such as an Access database) and then output into Excel for analysis purposes.  You could then potentially receive your StudentStatus worksheet directly from the database.

 

There is a video lesson explaining this in the upcoming Excel 2013 Expert Skills course, which will be available within the next couple of weeks.  If you have the printed book version of the course, you'll find that this is Expert Skills Lesson 6-1 Understand primary and foreign keys.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 25 November 2014 - 09:46 PM

Well, I guess in my case, all roads lead to a relational database. :)

 

 

I've been meaning to take a closer look into that book, so maybe now's the time.

 

As always, thanks for your help.



#4 AExcel

AExcel

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 02 October 2017 - 07:52 PM

Agreed that VLOOKUP is far simpler, however INDEX:MATCH does show its face a fair amount in management accounting (the only field I can speak about), so would be great if a future course had a quick overview of it. :)



#5 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 03 October 2017 - 08:17 PM

Hi AExcel,

 

Our courses aren't intended to be reference manuals, so we make no attempt to cover every function in Excel (there are over 450!). Instead our courses try to equip you with the skills you need to use any of the functions in Excel's vast library.

 

That said, you're not the first to ask us to cover INDEX and MATCH, and we have added a brief overview of INDEX and MATCH to our Excel 2016 course, as well as some information on using MATCH to make VLOOKUP functions more resilient to workbook changes.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users