Excel - INDEX Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need help with a formula that uses index and match along with small, row and if statements to return multiple rows and columns of data from a matrix
Solved by Z. W. in 39 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 19/06/2018 - 04:28
Hello?
Excelchat Expert 19/06/2018 - 04:28
Hello Welcome to Gotit pro, your problem will be highly answered here. May I ask how may I help you?
User 19/06/2018 - 04:29
Sure! Thanks!!
Excelchat Expert 19/06/2018 - 04:29
it looks you want help on your formula?
User 19/06/2018 - 04:29
can i edit the speadsheet so that you can see?
Excelchat Expert 19/06/2018 - 04:29
uld i ask your file so we can check it
Excelchat Expert 19/06/2018 - 04:30
yes
Excelchat Expert 19/06/2018 - 04:30
you may paste their your data
User 19/06/2018 - 04:31
So my data looks like this
Excelchat Expert 19/06/2018 - 04:31
ok and then?
Excelchat Expert 19/06/2018 - 04:32
you want a index function?
User 19/06/2018 - 04:32
and i need formulas that will return Site, Specialty, Name_First and Name_Last, only if the category is Eligible
User 19/06/2018 - 04:33
yes i think its going to be an index function
Excelchat Expert 19/06/2018 - 04:33
ok, and then in which cell you want the formula?
User 19/06/2018 - 04:34
im creating a seperate dashboard sheet to display the data
Excelchat Expert 19/06/2018 - 04:34
ok
User 19/06/2018 - 04:34
any cell you choose will be perfect. i just need to know how to make the formula work
Excelchat Expert 19/06/2018 - 04:35
ok, just give me a minute to work into this
User 19/06/2018 - 04:35
no worries! thank you so much!!
Excelchat Expert 19/06/2018 - 04:37
could you check the one i created
User 19/06/2018 - 04:38
That looks great. Is there a way to get rid of the empty space?
Excelchat Expert 19/06/2018 - 04:39
you mean skip it?
User 19/06/2018 - 04:40
Ideally, i would like to type in a Site, and have the rows populate with the appropriate data. So if i type "UMass" into a cell, i want the table to populate with all of data for the "Eligible" people
Excelchat Expert 19/06/2018 - 04:40
oh i see
Excelchat Expert 19/06/2018 - 04:41
i will do another one
User 19/06/2018 - 04:41
thank you!
Excelchat Expert 19/06/2018 - 04:55
here you go
Excelchat Expert 19/06/2018 - 04:56
you may try to type the site name on D10
Excelchat Expert 19/06/2018 - 04:56
and it will automatically call on the below table, all those eligible
User 19/06/2018 - 04:56
Thats fantastic, thank you so much!!!!!
Excelchat Expert 19/06/2018 - 04:56
great :)
Excelchat Expert 19/06/2018 - 04:57
is it what you are looking for?
Excelchat Expert 19/06/2018 - 04:58
i added some parameters, so we could lookup the value which are eligible only
Excelchat Expert 19/06/2018 - 04:58
by typing the site
User 19/06/2018 - 04:58
yes, this is perfect
Excelchat Expert 19/06/2018 - 04:58
great, :):)
User 19/06/2018 - 04:58
will this work with duplicates as well?
Excelchat Expert 19/06/2018 - 04:58
i'm happy to help tou :)
Excelchat Expert 19/06/2018 - 04:59
yes
Excelchat Expert 19/06/2018 - 05:00
wait i will fix some minor
Excelchat Expert 19/06/2018 - 05:00
for us we can use even there was a duplicate
Excelchat Expert 19/06/2018 - 05:05
here it is :0
Excelchat Expert 19/06/2018 - 05:06
so even we have duplicates it will still be call on the below
Excelchat Expert 19/06/2018 - 05:06
like that, i tried to dupicate the Umass
User 19/06/2018 - 05:06
this is awesome.
User 19/06/2018 - 05:06
youre the best!
Excelchat Expert 19/06/2018 - 05:07
thanks :)
Excelchat Expert 19/06/2018 - 05:07
Is there anything else you want to know regarding this issue?
User 19/06/2018 - 05:07
i think i am all set!
Excelchat Expert 19/06/2018 - 05:07
nice :)
Excelchat Expert 19/06/2018 - 05:07
Thanks for using Got It Pro. Please give your kind feedback for our service. On your window, kindly don't just close the window, there is an end button there so you could end the session properly & message will appear to ask for the feedback :) Have a good day ahead ^_^

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.