Ignore blanks in Index/Match

Can anyone suggest how to edit this code to ignore any blanks in column AC?

Thanks

Please Login or Register  to view this content.

We can't really see what it's doing...

Is there any reason why you are using the old IF(ISERROR( style of error trap? Is the sheet being shared with users with Excel 2003 and earlier?

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

can you also explain what you WANT the formula to do??

That doesn't help!!

What is in K4 and K5? Where is the formula? In the case of the attached file, what are the expected results?

If you can upload dummy data, modify my sample file and upload it. Otherwise... (you're a teacher)... use your language skills to describe everthing succinctly and clearly.


Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Hi. OK. I have twiddled with your formula, to make it more efficient (only calculating once, and a non array formula). Now that I understand what's going on... what changes do you need , or have I accidentally solved your problem. Hidden columns have been deleted.

=IFERROR(INDEX($X:$X,AGGREGATE(15,6,ROW($Y$5:$Y$34)/($Y$5:$Y$34<=-2),ROWS($1:1))),"")

and similar...

Thanks for you time on this and your code is certainly is much more efficient.

However my original issue remains. Column G (in your updated version) is still including pupils with blanks in column Y (eg Yasmine)

Simples... to coin a phrase... try this, variants of:

=IFERROR(INDEX($X:$X,AGGREGATE(15,6,ROW($Y$5:$Y$34)/(($Y$5:$Y$34<=-2)*($Y$5:$Y$34<>"")),ROWS($1:1))),"")

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.