Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-07-2016, 06:34 PM
danigirl121 danigirl121 is offline How to make MATCH/INDEX formula return multiple successive responses. Windows 10 How to make MATCH/INDEX formula return multiple successive responses. Office 2013
Novice
How to make MATCH/INDEX formula return multiple successive responses.
 
Join Date: Jun 2016
Posts: 4
danigirl121 is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 06-07-2016, 08:12 PM
macropod's Avatar
macropod macropod is offline How to make MATCH/INDEX formula return multiple successive responses. Windows 7 64bit How to make MATCH/INDEX formula return multiple successive responses. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 06-07-2016, 09:23 PM
danigirl121 danigirl121 is offline How to make MATCH/INDEX formula return multiple successive responses. Windows 10 How to make MATCH/INDEX formula return multiple successive responses. Office 2013
Novice
How to make MATCH/INDEX formula return multiple successive responses.
 
Join Date: Jun 2016
Posts: 4
danigirl121 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 06-07-2016, 09:48 PM
macropod's Avatar
macropod macropod is offline How to make MATCH/INDEX formula return multiple successive responses. Windows 7 64bit How to make MATCH/INDEX formula return multiple successive responses. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 06-07-2016, 10:31 PM
danigirl121 danigirl121 is offline How to make MATCH/INDEX formula return multiple successive responses. Windows 10 How to make MATCH/INDEX formula return multiple successive responses. Office 2013
Novice
How to make MATCH/INDEX formula return multiple successive responses.
 
Join Date: Jun 2016
Posts: 4
danigirl121 is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 06-07-2016, 10:38 PM
macropod's Avatar
macropod macropod is offline How to make MATCH/INDEX formula return multiple successive responses. Windows 7 64bit How to make MATCH/INDEX formula return multiple successive responses. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

There has to be a column to match against, even if that column is hidden.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 06-08-2016, 08:25 AM
danigirl121 danigirl121 is offline How to make MATCH/INDEX formula return multiple successive responses. Windows 10 How to make MATCH/INDEX formula return multiple successive responses. Office 2013
Novice
How to make MATCH/INDEX formula return multiple successive responses.
 
Join Date: Jun 2016
Posts: 4
danigirl121 is on a distinguished road
Default

How would the formula change if I were to be using it in Google Docs instead of Excel?
Reply With Quote
  #8  
Old 06-08-2016, 01:57 PM
macropod's Avatar
macropod macropod is offline How to make MATCH/INDEX formula return multiple successive responses. Windows 7 64bit How to make MATCH/INDEX formula return multiple successive responses. Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

I don't use Google products, so I couldn't say.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
index match

Thread Tools
Display Modes


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
How to make MATCH/INDEX formula return multiple successive responses. 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:24 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft