google sheets - Combine IF for text with INDEX and MATCH? Or VLOOKUP? QUERY? - Stack Overflow

admin管理员组

文章数量:1122832

Coming back to the best source I know to ask for help with something I'm trying to do for our little school. I have one sheet where I enter the date and the name of the student under what they're supposed to do for dismissal (car rider, bus, or aftercare). But teachers are finding it too difficult to go through many students at one to find their own, so I'd like them to each have their own tab with only their students. So a function should pull the name of the student from "Changes," match it to the "Student Reference" tab, then put it on the tab of the teacher in charge of that student.

I can just barely manage INDEX and MATCH alone, and conditions alone, but can't even start to figure out how to go about what I want to do, even though surely it must be possible.

Thank you soooo much in advance for any help! This site has taught me so much.

Coming back to the best source I know to ask for help with something I'm trying to do for our little school. I have one sheet where I enter the date and the name of the student under what they're supposed to do for dismissal (car rider, bus, or aftercare). But teachers are finding it too difficult to go through many students at one to find their own, so I'd like them to each have their own tab with only their students. So a function should pull the name of the student from "Changes," match it to the "Student Reference" tab, then put it on the tab of the teacher in charge of that student.

I can just barely manage INDEX and MATCH alone, and conditions alone, but can't even start to figure out how to go about what I want to do, even though surely it must be possible.

Thank you soooo much in advance for any help! This site has taught me so much.

• google-sheets
• match
• vlookup

Share Improve this question Follow edited Nov 22, 2024 at 16:23 Sarah Savoy asked Nov 22, 2024 at 15:18 Sarah SavoySarah Savoy 1555 bronze badges 2

• docs.google.com/spreadsheets/d/… – Sarah Savoy Commented Nov 22, 2024 at 15:18
• Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented Nov 23, 2024 at 23:17

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

Try this formula in Cell_A2 of JANE tab. Change the teacher name within the formula while applying it to the other tabs:

=reduce(torow(,1),sequence(1,3,1,2),lambda(a,c,ifna(hstack(a,ifna(filter(choosecols(Changes!A:F,c,c+1),xlookup(choosecols(Changes!A:F,c+1),'Student Reference'!D:D,'Student Reference'!C:C,)="JANE"),wraprows(,2,))))))

本文标签： google sheetsCombine IF for text with INDEX and MATCH Or VLOOKUP QUERYStack Overflow