Forum Discussion
Formula or Function???
=IFERROR(INDEX($AG$16:$BQ$16,SMALL(IF(OFFSET($G$1,MATCH($BU$21,$G$23:$G$435,0)+21,26,1,37)=1,COLUMN($A:$AK)),ROW(1:1))),"")You are welcome. You can try this formula which works in my file. The formula has to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021.
Assuming you run 2021 or 365 see one way in attached file
Thanks again for you time! If you have other ideas I'm open!
Alicia
My table is much more complex then just those simple headers and the headers are not even in same row. Unfortunately it was a sheet not originally designed by me. My names are in G23 to G435 and my course title headers are in AG16 to BQ16
This looks to be an "interesting" setup. Could you please:
- Share a representative workbook (replace actual names with Name1, Name2...) i.e. https://support.microsoft.com/en-us/office/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07 or any other file sharing service
- Clarify which version of Excel you run
So Lz solution is basically what I would suggest. Have a separate tab for them to enter their name and see their course list. Lz applied "Data Validation" to cell C2 to create a drop down list of all students so they can easily select their name. The source for that list uses a 'name' called StudentNames which you will have to define to match the range that has your student names (i.e. G23:G435). The formula below that is:
=IF(ISBLANK(C2), "", FILTER(Table1[#Headers],FILTER(Table1, Table1[Name]=C2)=1))
Where it is looking up the courses in "Table1". you can/should select the data range and "format as a table" to use this format (this is the recommended/preferred way). alternatively you can convert that formula to use range references (which I think would be)
=IF(ISBLANK(C2), "", FILTER(SHEET1!AG16:BQ16,FILTER(SHEET1!AG23:BQ435, G23:G435=C2)=1))that all said, if you are getting a #NAME? error then you might not have the newish FILTER function. What version of Excel are you using?
Another option might be to create a pivot table from the full set of data and let them select their name from a filter field.
Can you attach a version of the sheet with the names de-identified so we can more easily show you how?
