Change Index/Match Formula to Skip if formula returns N/A?

Hi All,

I was wondering if it would be possible for this formula to be adapted so that if it returns #n/a, it will just skip and look in the next cell & also return all values that meet the criteria instead of the just the first value that does.

Please see the formula below:

Please Login or Register  to view this content.

I hope that this may be possible.

Thanks

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thank you for your response,

I have attached a workbook that i think helps show what i am looking for.

The entries tab is where the formula is located and the GCSE tab is where the data is being pulled from.

I hope this gives you a better understanding.

Thanks

I am "out of the office" for a couple of hours but hopefully someone will respond before I return.

See the attached:

In "Entries" column J contains a list of courses and is a named range called "Courses"

B2 has a drop-down of the course list

Column B has formula which selects on course in B2

=IFERROR(INDEX('Full GCSE & SC GCSE'!$A$3:$A$34,SMALL(IF(INDEX('Full GCSE & SC GCSE'!$P$3:$CY$34,,MATCH($B$2,'Full GCSE & SC GCSE'!$P$1:$CY$1,0))="Y",ROW('Full GCSE & SC GCSE'!$A$3:$A$34)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")

Enter with Ctrl+Shift+Enter

If done correctly, you will see brackets { ...} appear round the formula

I have removed merged cells: do NOT use merged cells as they cause problems particularly with formulae.

Thanks John,

That works perfect! I really appreciate this!!

I have another quick question would it be possible to make this work for the result of the course as well?
So that next to the student name it would produce the result for that student in the course?
Would this be able to be done by adapting the formula already being used or would this need another tactic?

Thanks

James

Which column is the result ... "Actual Grade" ?

Yes sorry that is correct. Should of mentioned that

Thank you very much!
I can see how this works a little bitmore now!
I am encountaring one issue as further up the sheet the column would need to be three not plus 4, i can solve this by just adding a extra coloumn that just gets left blank, unless.there is another way to get round this!?