Forum Discussion
Solved
Formula or Function???
I have an excel spreadsheet that is huge (over 400 names and over 30 courses) and I need an easy way for a person to see their specific course requirements. Not sure if they would click on their name...
=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.
=IFERROR(INDEX($B$1:$H$1,SMALL(IF(OFFSET($A$1,MATCH($J$4,$A$2:$A$22,0),1,1,7)=1,COLUMN($A:$G)),ROW(1:1))),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. In cell J4 you can enter the name for which you want to return the courses. The formula is in cell J6 and filled across range J6:J12. The ranges of the formula can be adapted to the actual size of the data e.g. $A$2:$A$22 can be changed to $A$2:$A$600.
- Quadruple Pawn,
Thanks so much for your response! It worked perfectly when I utilized in a table as above but when I tried to duplicate it in the actual table it didn't work for some reason?? My names are in G23 to G435 and my course title headers are in AG16 to BQ16. I am attempting to make BU21 my input box and added formula in BU24:BU50 with no results. Here is how I rewrote the formula (would you mind looking at it and see if you see any errors??):
=IFERROR(INDEX($AG$16:$BQ$16,SMALL(IF(OFFSET($G$21,MATCH($BU$21,$G$22:$G$535,0),1,1,7)=1,COLUMN($G:$BQ)),ROW(1:1))),"")
Thanks again!
Alicia=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.
- WORKS!!! Thank you so much!! I just hid all the columns except the drop down list for people to choose their names alphabetically and they get their own list! Glorious!! Thank you!!
Alicia
