INDEX/MATCH array function returning nothing for some queries but not others

Hi everyone,

I've been having difficulty with a sheet I've been putting together. Essentially, it is a list of people and their responsibilities VLOOK-ed-UP from another spreadsheet, which in turn is indexed with a multiple result array formula, shown below:

{=IF(ISERROR(INDEX($AA$1:$AC$345,SMALL(IF($AC$1:$AC$345="U",ROW($AC$1:$AC$345)),ROW(1:1)),1)),"",INDEX($AA$1:$AC$345,SMALL(IF($AC$1:$AC$345="U",ROW($AC$1:$AC$345)),ROW(1:1)),1))}

With "U" being the name of the person for whom the results are being returned. This is copied multiple times across the table for different people and ranges, and works just fine, but in two columns, for the same person, the formulas go blank, like there's nothing in the table. I've tried formatting the whole table, all kinds of permutations of testing, but I just can't work out why the same formula, searching the same set of data generated in the same way, would return a negative result.

Any help or suggestions enormously appreciated,

JB

Welcome to the forums!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are 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 data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as 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.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

It's a trailing space problem - look at the cell containing Alpha on Sheet3 - put your cursor in the cell and you'll see there's a trailing space - that's why there is no match.

You would be far, far better off sanitising your data than adding TRIM to everything!