+ Reply to Thread
Results 1 to 8 of 8

Index Match with nested isnumber

  1. #1
    Registered User
    Join Date
    03-03-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    31

    Index Match with nested isnumber

    I am trying to do a index match on employee number and academic grade and return the academic grade. However I am looking for the academic grade to be searched against its qualification (i.e. Bachelor or Masters) which ignores if it is a 'Bachelor of Science' or 'Bachelor (Hons)', etc.

    I can't quite work the isnumber function into the index match so any help appreciated.

    James
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    02-07-2013
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    290

    Re: Index Match with nested isnumber

    Hello James,

    Can you post more data with (manually) expected output?

    Regards,
    Khalid

  3. #3
    Registered User
    Join Date
    03-03-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    31

    Re: Index Match with nested isnumber

    I have added an example of the search requirements and the desired outputs. Hopefully this makes my enquiry clearer but any other questions please let me know.

    Thanks for your help.

    James
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    02-07-2013
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    290

    Re: Index Match with nested isnumber

    I think you can use this {array formula} in E14:

    =INDEX($F$3:$F$6,MATCH(E11&E10,MID($F$3:$F$6,FIND(" ",$F$3:$F$6)-LEN(E11),LEN(E11))&$C$3:$C$6,0))

    Enter with Ctrl+Shift+Enter (not just enter)

    then you can copy/past to E22

    Blessing

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,707

    Re: Index Match with nested isnumber

    e14
    Please Login or Register  to view this content.
    Try this in E14 AND COPY and paste it in E22
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,900

    Re: Index Match with nested isnumber

    Another ..

    =INDEX($F$3:$F$6,MATCH(1,($C$3:$C$6=E10)*(ISNUMBER(SEARCH(E11,$F$3:$F$6))),0))

    Enter with Ctrl+Shift+Enter

  7. #7
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,639

    Re: Index Match with nested isnumber

    Or try this... =LOOKUP(2,1/($C$3:$C$6=E10)/(LEFT($F$3:$F$6,LEN(E11))=E11),$F$3:$F$6)

  8. #8
    Registered User
    Join Date
    03-03-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    31

    Re: Index Match with nested isnumber

    Thank you nflsales, working brilliantly

+ 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. If nested index match
    By trinialaskan in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-29-2016, 10:39 AM
  2. [SOLVED] Index with Nested Match Function Help
    By T86157 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-30-2015, 12:43 PM
  3. [SOLVED] Nested Index Match
    By Knawl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-07-2014, 07:39 AM
  4. [SOLVED] 3 Nested IF INDEX MATCH
    By JonesZoid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-09-2014, 10:56 AM
  5. USING IF ISTEXT OR ISNUMBER THEN INDEX MATCH OR calculation
    By pippib in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-17-2012, 01:26 AM
  6. Nested Match(index()) vs. Match() array formula.
    By GeneralDisarray in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2011, 07:57 AM
  7. Nested IF (index and match)
    By mikera in forum Excel General
    Replies: 2
    Last Post: 01-21-2010, 11:27 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