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
Hello James,
Can you post more data with (manually) expected output?
Regards,
Khalid
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
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
e14Try this in E14 AND COPY and paste it in E22
Please Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
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
Or try this... =LOOKUP(2,1/($C$3:$C$6=E10)/(LEFT($F$3:$F$6,LEN(E11))=E11),$F$3:$F$6)
Thank you nflsales, working brilliantly
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks