Index Match Multiple Results - Not able to generate a value using array entry

**jrr123** · 04-23-2015, 09:12 AM

Good day,

I receive a exported file from a Project tracking system we have that details projects, and the personnel assigned to those projects along with the % of time required per month. I am trying to build a sheet that would summarize the effort by person using Index Match (as there are some naming conventions on the export that prevent the use of a pivot table).

I am able to get a summary of the data, but not able to pull the names of the projects each resource is assigned to.

I have attached a sample sheet. On the PMO tab, there is a resource listed, and a summary of the time assigned to projects on the "Roadmap" tab and on the vacation tab.

I am trying to list the specific projects below the resource using the formula:

=IFERROR(INDEX('Roadmap w Resources'!$O$1:$O$1000,SMALL(IF('Roadmap w Resources'!$A$1:$A$1000="*"&$B$4&"*",ROW('Roadmap w Resources'!$A$1:$A$1000)-ROW('Roadmap w Resources'!$A$1:$A$1000)+1),ROWS($B$7:B7))),"")

Which does list the first entry for this resource, however when I enter as an array I get no data.

Please let me know if you require further information.

Thanks so much for the assistance!

Sample.xlsx

Jon

**XOR LX** · 04-23-2015, 09:22 AM

Hi.

Two points:

Firstly, you cannot use wildcards like that within a simple IF statement. The only reason that you got any result at all in your first cell was via a series of fortunate coincidences.

Secondly, your construction for generating the row_num parameter for INDEX is not correct: it should be:

ROW('Roadmap w Resources'!$A$1:$A$1000)-MIN(ROW('Roadmap w Resources'!$A$1:$A$1000))+1

All in all, you need:

=IFERROR(INDEX('Roadmap w Resources'!$O$1:$O$1000,SMALL(IF(ISNUMBER(FIND($B$4,'Roadmap w Resources'!$A$1:$A$1000)),ROW('Roadmap w Resources'!$A$1:$A$1000)-MIN(ROW('Roadmap w Resources'!$A$1:$A$1000))+1),ROWS($B$7:B7))),"")

Note that FIND is case-sensitive. Change to SEARCH if this is undesirable

Regards

**jrr123** · 04-23-2015, 09:40 AM

Thank you very much for the prompt response, you solved the problem I was having. Thank you so much!

Unfortunately I now see an additional condition I need added to the formula, as the Resource name may appear in the Project Heading as well as being a resource on the Project. The condition I needed added is to only count the result if there is a value over 0 in columns B through N on the Resource worksheet for the matching name.

Thanks again for looking at this!

**XOR LX** · 04-23-2015, 09:47 AM

Originally Posted by jrr123

The condition I needed added is to only count the result if there is a value over 0 in columns B through N on the Resource worksheet for the matching name.

So that condition needs to be satisfied in all of those columns for a count to be generated for that row? Or in at least one?

Regards

**jrr123** · 04-23-2015, 09:48 AM

At least one column

**XOR LX** · 04-23-2015, 09:56 AM

Thanks.

=IFERROR(INDEX('Roadmap w Resources'!$O$1:$O$1000,SMALL(IF(ISNUMBER(FIND($B$4,'Roadmap w Resources'!$A$1:$A$1000)),IF(MMULT((ISNUMBER('Roadmap w Resources'!$B$1:$N$1000)*('Roadmap w Resources'!$B$1:$N$1000>0)),TRANSPOSE(COLUMN('Roadmap w Resources'!$B$1:$N$1000)^0)),ROW('Roadmap w Resources'!$A$1:$A$1000)-MIN(ROW('Roadmap w Resources'!$A$1:$A$1000))+1)),ROWS($B$7:B7))),"")

By the way, by using an upper range reference of 1000 you are forcing Excel to perform a massive amount of unnecessary work here (your data appears to end at row 25).

You should know that you cannot just choose an arbitrarily large value here, with no detriment to performance. Unlike e.g. COUNTIF(S)/SUMIF(S), array formulas calculate over all cells passed to them, whether technically beyond the last-used cells in those ranges or not.

Regards

**jrr123** · 04-23-2015, 09:59 AM

Thanks again! That worked.

The sample was limited to a few projects but my actual sheet borders on 800 rows and has the potential to expand beyond that, which is the reason I selected 1000.

I appreciate the help!

**XOR LX** · 04-23-2015, 10:02 AM

Ah, ok. In that case 1000 is a good choice!

And you're welcome!

Cheers

Index Match Multiple Results - Not able to generate a value using array entry

LinkBack

Thread Tools

Rate This Thread

Display

Index Match Multiple Results - Not able to generate a value using array entry

Re: Index Match Multiple Results - Not able to generate a value using array entry

Re: Index Match Multiple Results - Not able to generate a value using array entry

Re: Index Match Multiple Results - Not able to generate a value using array entry

Re: Index Match Multiple Results - Not able to generate a value using array entry

Re: Index Match Multiple Results - Not able to generate a value using array entry

Re: Index Match Multiple Results - Not able to generate a value using array entry

Re: Index Match Multiple Results - Not able to generate a value using array entry

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Sort results of INDEX/MATCH array formula remove blanks. . .

[SOLVED] Index, Match, Multiple Results

Index/Match with multiple results

[SOLVED] Index/Match Array with multiple results concatenate in one cell

Index and Match and multiple results

Bookmarks

Bookmarks

Posting Permissions