+ Reply to Thread
Results 1 to 6 of 6

Formula containing index match isnumber and search with a few Ifs thrown in, not working

  1. #1
    Registered User
    Join Date
    04-20-2017
    Location
    Christchurch, Dorset
    MS-Off Ver
    2016
    Posts
    29

    Formula containing index match isnumber and search with a few Ifs thrown in, not working

    Hi guys

    I've been struggling with a formula all afternoon and thought I had it working a few times - I was sadly mistaken.

    This spreadsheet is for checking that data has been entered for each student for every subject that they study - it is not obvious from the spreadsheet how difficult that is to see because the spreadsheet I have attached is so cut down from the one that we actually use. I can only say that this spreadsheet saves us hours of work every time we collect data for students - anyway I digress!

    Most subjects work as they are very straigtforward - I've left a Spanish column in showing the formulas that we normally use. however, the Technology subjects are different in that the class names state which strand of technology the student is studying but all the grades are collected in one column - they only study one strand a term so there is never the need for grades for each strand and this makes reporting much much easier so we don't want to change the way we work.

    We run a report that lists all the classes that students take and paste it into the table in StudentClass - here you can see the three types of technology that are studied by the students.

    Data is also pasted into Subjects Status, in the grey areas of the table with the numeric grades they have achieved this data harvest.

    The yellow headed columns check if the student studies a technology class and if so, checks for a grade in the grey cells, if there is not one there it returns "msng" - I have got this part working

    The green headed columns check the yellow area for cells that contain "msng" and then I want the class name returned instead. I thought it was working but then realised it was only returning the final default option, the if(isnumber(search wasn't doing what I thought it was.

    Can anyone help please?

    Thank you in advance

    Sue
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula containing index match isnumber and search with a few Ifs thrown in, not worki

    Please try at L4:M12

    =IF([@[Technology - WA Grade Check]]="msng",INDEX(StudentClass,MATCH($D4&"*t*",INDEX(StudentClass,,8),),6),"")

    The above formula just show any class that match with Admission No. and subject code content "t".

    I don't know which class do you want to show in column L for WA Class or M for BLP Class.
    Please mock up manual result of what you want in L4:M12

  3. #3
    Registered User
    Join Date
    04-20-2017
    Location
    Christchurch, Dorset
    MS-Off Ver
    2016
    Posts
    29

    Re: Formula containing index match isnumber and search with a few Ifs thrown in, not worki

    Hi Bo_Ry

    Thanks for looking at the spreadsheet for me.

    I've updated the spreadsheet attached to include columns O and P showing what I would expect to see in columns L and M. These are the results from the StudentClass sheet.

    Using *t* unfortunately brings up the first one of the many other classes that contain a T that are not shown in this cut down version (It, Ct, etc.)

    I've also added back in some Spanish classes in the StudentClass sheet so that you can see what happens with all other subjects. As I said, Technology is the only subject I am having problems with because the class codes can be a variety of things, in the example attached they could be Ft, Tx or Te.

    I hope that is a bit clearer?

    Many thanks

    Sue
    Attached Files Attached Files
    Last edited by SueBristow; 11-21-2018 at 04:25 AM.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula containing index match isnumber and search with a few Ifs thrown in, not worki

    Please try

    =IF([@[Technology - WA Grade Check]]="msng",INDEX(StudentClass,AGGREGATE(15,6,MATCH($D4&{"Ft","Tx","Te"},INDEX(StudentClass,,8),),1),6),"")

  5. #5
    Registered User
    Join Date
    04-20-2017
    Location
    Christchurch, Dorset
    MS-Off Ver
    2016
    Posts
    29

    Re: Formula containing index match isnumber and search with a few Ifs thrown in, not worki

    Wow - thank you! I have absolutely no idea what it is doing but it is working! I didn't enter it as an array (the {} made me think it should be) but it works anyway.

    Many Many thanks

    Sue

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula containing index match isnumber and search with a few Ifs thrown in, not worki

    Hi Sue,

    Happy to help.

    MATCH($D4&{"Ft","Tx","Te"},INDEX(StudentClass,,8),) this will return row value that match "Ft","Tx","Te" and others that not match will get #N/A.

    AGGREGATE(15 for small or 14 for large can use both to extract row from match() , 6 for ignore error like #N/A #DIV/0!, match() , 1)
    AGGREGATE can handle array without CSE.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] My =IF(ISNUMBER(SEARCH Formula is Not Working
    By thanhie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-03-2022, 12:30 AM
  2. [SOLVED] Using ISNUMBER SEARCH with INDEX MATCH
    By Emile du Toit in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-15-2020, 08:54 AM
  3. IF(ISNUMBER(SEARCH Fromula from multiple cells not working
    By RossCR in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-16-2016, 11:41 PM
  4. IF ISNUMBER SEARCH AND ISERROR VLOOKUP INDEX MATCH in formula - out of my depth ;)
    By Emile du Toit in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-20-2016, 07:29 AM
  5. Index Match with nested isnumber
    By JamesArmitage in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-22-2016, 08:04 AM
  6. [SOLVED] Working INDEX MATCH with SEARCH, but I need to add another MATCH to the formula!
    By DaveBre in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-11-2014, 01:03 AM
  7. Isnumber thrown off by rogue spaces
    By jennyaccord in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2012, 11:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1