Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-23-2021, 03:25 PM
Bosslb Bosslb is offline Index Match and return mutiple results from a table Windows 7 64bit Index Match and return mutiple results from a table Office 2019
Novice
Index Match and return mutiple results from a table
 
Join Date: Sep 2021
Posts: 1
Bosslb is on a distinguished road
Exclamation Index Match and return mutiple results from a table

Hello,



I am currently using this formula to return data from one table that has a "Student ID" column.

Sometimes the Student ID is repeated in the table and so I want to return all the rows that has the same Student ID in that table.

With this formula, I am getting just the 1st row!



=IF(INDEX(All_Appended[#Data], MATCH($O$3,All_Appended[Student ID],0), MATCH(D6,All_Appended[#Headers],0))=0, "-",INDEX(All_Appended[#Data],MATCH($O$3,All_Appended[Student ID],0), MATCH(D6,All_Appended[#Headers],0)))





Anyone can help please to make this work?

Thank you.
Reply With Quote
  #2  
Old 09-23-2021, 09:37 PM
Marcia's Avatar
Marcia Marcia is offline Index Match and return mutiple results from a table Windows 10 Index Match and return mutiple results from a table Office 2019
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 427
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

It would be simpler for others to help you if you attach a sample sheet.
Reply With Quote
  #3  
Old 09-23-2021, 11:18 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Index Match and return mutiple results from a table Windows 7 64bit Index Match and return mutiple results from a table Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,480
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Take a look at INDEX MATCH – multiple results
Technique is described there


(O365 offers other tools AFAIK like the FILTER function)
Reply With Quote
  #4  
Old 09-24-2021, 09:12 AM
Purfleet Purfleet is offline Index Match and return mutiple results from a table Windows 10 Index Match and return mutiple results from a table Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 335
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Quote:
Originally Posted by Marcia View Post
It would be simpler for others to help you if you attach a sample sheet.
It drives me mad! The worksheet almost certainly exists in front of them but people not add it!
Reply With Quote
  #5  
Old 09-24-2021, 09:14 AM
Purfleet Purfleet is offline Index Match and return mutiple results from a table Windows 10 Index Match and return mutiple results from a table Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 335
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Quote:
Originally Posted by Bosslb View Post
Hello,

I am currently using this formula to return data from one table that has a "Student ID" column.

Sometimes the Student ID is repeated in the table and so I want to return all the rows that has the same Student ID in that table.

With this formula, I am getting just the 1st row!



=IF(INDEX(All_Appended[#Data], MATCH($O$3,All_Appended[Student ID],0), MATCH(D6,All_Appended[#Headers],0))=0, "-",INDEX(All_Appended[#Data],MATCH($O$3,All_Appended[Student ID],0), MATCH(D6,All_Appended[#Headers],0)))





Anyone can help please to make this work?

Thank you.
That is exsactly what index and match do (and vlookup) it finds the first one.

if you want it to find all it will be a lot more work, but sometimes a Pivot table might be a quick win.

As Marcia said always add a work sheet example
Reply With Quote
  #6  
Old 09-25-2021, 12:34 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Index Match and return mutiple results from a table Windows 7 64bit Index Match and return mutiple results from a table Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,480
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Quote:
Originally Posted by Purfleet View Post
That is exsactly what index and match do (and vlookup) it finds the first one.

if you want it to find all it will be a lot more work, but sometimes a Pivot table might be a quick win.

As Marcia said always add a work sheet example

If you follow the link I provided you will find that a simple CSE formula suffices
It's not that complicated
Reply With Quote
  #7  
Old 09-25-2021, 11:26 PM
Purfleet Purfleet is offline Index Match and return mutiple results from a table Windows 10 Index Match and return mutiple results from a table Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 335
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

I put that formula into the complex category and it can have an impact on performance
Reply With Quote
  #8  
Old 09-25-2021, 11:42 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Index Match and return mutiple results from a table Windows 7 64bit Index Match and return mutiple results from a table Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,480
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Quote:
Originally Posted by Purfleet View Post
I put that formula into the complex category and it can have an impact on performance

Complexity is in the eye of the beer holder
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index Match and return mutiple results from a table Index Results Help radicalrom Excel 7 01-06-2019 06:02 AM
Index Match and return mutiple results from a table Index Match Formula to return value from a list with criteria Marcia Excel 4 10-26-2018 08:59 AM
need Index, Match, Max to return SUMPRODUCT? IRHSAM Excel 0 11-17-2017 09:48 AM
Excel - Index and Match Function ,First Second and Third Match paulzy95 Excel 10 09-29-2016 10:46 PM
Index Match and return mutiple results from a table How to make MATCH/INDEX formula return multiple successive responses. danigirl121 Excel 7 06-08-2016 01:57 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:09 AM.


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