#1
|
|||
|
|||
How to make MATCH/INDEX formula return multiple successive responses.
I'm using an Index/Match formula to run down a column with teacher names. There are 8 teachers used multiple times in 82 rows. I want to search the column for a specific teacher, and have the corresponding student show up. Right now it just returns the first student on the list. How do I make it recognize that student was already listed and move on down the list to the next student in the next cell? So A2 on the teacher sheet populates with a student name. I want A3 to populate with the next student name. A2:A83 currently return the same student over and over.
=INDEX(StudentName, MATCH("TeacherName", TeacherName, 0)) StudentName and TeacherName are both set ranges from A2:A83 and G2:G83 on the my master sheet respectively. I'm trying to pull the information from a master sheet to a sub-sheet assigned to the teacher. |
#2
|
||||
|
||||
See, for example:
http://windowssecrets.com/forums/sho...l=1#post734296 http://www.techsupportforum.com/foru...ml#post2567119
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Looking at the spreadsheet you sent. I'm not sure how to make it apply to what I need. I only want one column of information to fill in on the subsequent sheets while pulling information from 2 columns in the mastersheet. I was looking at the following formula to try to alter it, but can't quite figure it out.
=IF($A3="","",INDEX(Data!B$1:B$23,MATCH($A$2,OFFSE T(Data!$A$1,MATCH($C2,Data!$C$1:$C$23,0),0,COUNT(D ata!$A$1:$A$23)+1-MATCH(B2,Data!B$1:B$23,0),1)ata!$A$23,0)+MATCH($ C2,Data!$C$1:$C$23,0))) I want to search column G on my master sheet for the teacher's name and if it matches then I want the student name in Column A that is in the same row to populate on my subsheet in column A. I guess then use the OFFSET and COUNT to make it move down the column without populating the same student name twice. Using the MATCH A and MATCH B was sort of working for me, but I don't know how to make the formula work by taking out MATCH C. |
#4
|
||||
|
||||
If you insert 5 columns after column A on the 'Data' sheet and change the 'Data' sheet name to match your own, the references in the formulae in the '500' and '508' sheets will update to match your needs.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
That doesn't get rid of column A in the 500 and 508 sheets. The formula only works if column A is there for it to read from.
|
#6
|
||||
|
||||
There has to be a column to match against, even if that column is hidden.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
How would the formula change if I were to be using it in Google Docs instead of Excel?
|
#8
|
||||
|
||||
I don't use Google products, so I couldn't say.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
index match |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
index / match Formula | dmcg9760 | Excel Programming | 1 | 11-08-2015 03:16 PM |
UDF multiple IFs INDEX-MATCH | grexcelman | Excel Programming | 0 | 02-22-2015 04:20 PM |
Excel Formula: return a range of cells that match | tinfanide | Excel | 4 | 08-30-2014 07:03 AM |
Complex Formula using INDEX and Match needed | OTPM | Excel | 5 | 05-23-2013 01:22 AM |
Help with multiple match and index formula | ryanwood | Excel | 1 | 09-12-2012 07:53 AM |