Forum Discussion
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 and get a link or popup or go to another sheet that has some formula that gives them just their courses. Any suggestions or assistance would be so appreciated!! Tried tons of things but can't figure it out! Here is an example:
| Name | BLS | ACLS | TNCC |
| Harris | 1 | 1 | |
| Johnson | 1 | 1 |
=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.
10 Replies
- In Excel, a formula is an expression that you create inside a cell, while a function is a predefined calculation in the spreadsheet application Here are some key differences between formulas and functions
A formula is any expression that begins with an equals sign (=).
A function is a formula with a special name and purpose.
Functions are predefined formulas that are already in Excel.
Functions carry out specific calculations in a specific order based on the values specified as arguments or parameters. =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.
Assuming you run 2021 or 365 see one way in attached file
- Thanks for reply Lz but for some reason even on example I am getting error and returning either blank or #NAME? response for anyone other than Johnson. I love the idea of a separate sheet though. Much easier for personnel then my huge spreadsheet. 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. Not sure if even adaptable to the formula you wrote?
Thanks again for you time! If you have other ideas I'm open!
AliciaMy 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
