# Index Match with nested isnumber

1. ## 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

2. ## Re: Index Match with nested isnumber

Hello James,

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

Regards,

3. ## 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.

James

4. ## 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. ## 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

6. ## 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. ## 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. ## Re: Index Match with nested isnumber

Thank you nflsales, working brilliantly

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

#### 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