array formula index and match not delivering expected results for certain rows

**JEAN1972** · 12-27-2017, 04:37 AM

I have a calculation sheet and a summary sheet in same workbook.

The summary sheet represent a summary of products totals costs of the working sheet which has several headers .

The headers concerned are columns C, D , E ,F,K and L of calculations sheets .

Using the sumproduct formula in summary sheet for columns K of sheet calculations

Please Login or Register  to view this content.

For reconciliation from summary sheet column I with calculations sheets of columns L array formula is used

Please Login or Register  to view this content.

But however I am not getting the correct results at row 4 ,11 ,12 highlighted in yellow of calculation sheets, column M represent the figures that it should show .

Can anyone assist what is going wrong .

**BadlySpelledBuoy** · 12-27-2017, 05:04 AM

Try this in L2 on the calculation sheet and copied down. It's an array formula so use Ctrl+Shft+Ent to confirm

Formula:

Please Login or Register  to view this content.

Does that solve the issue?

BSB

**tim201110** · 12-27-2017, 05:08 AM

formulas are correct, strings must be exact

**Glenn Kennedy** · 12-27-2017, 05:28 AM

Or a non-array version:
=INDEX(summary!F$2:F$13,MATCH(1,INDEX((summary!A$2:A$13=C2)*(summary!B$2:B$13=D2)*(summary!C$2:C$13=E2)*(summary!D$2:D$13=F2),0),0))

**shukla.ankur281190** · 12-27-2017, 05:39 AM

or can be use sumproduct give this try

Formula:

Please Login or Register  to view this content.

**JEAN1972** · 12-27-2017, 05:54 AM

@tim201110

Thanks I have adjusted , but one main issue as below

reading 1 instead of 2

That is row 4,11,12 should read 2 instead of 1 on calculation sheet

Please Login or Register  to view this content.

**BadlySpelledBuoy** · 12-27-2017, 05:58 AM

Any of the formulas in posts #2, #4 or #5 will give you the correct results.

BSB

**Glenn Kennedy** · 12-27-2017, 06:00 AM

=INDEX(summary!F$2:F$13,MATCH(1,INDEX((summary!A$2:A$13=C2)*(summary!B$2:B$13=D2)*(summary!C$2:C$13=E2)*(summary!D$2:D$13=F2),0),0))

works fine. By the way, the"concatenated" matches can be very slow and are best avoided.

**JEAN1972** · 12-27-2017, 06:32 AM

@Glen
@BSB
@Shulka

Your formula approaches works well, thank you

I have attached file with the three approaches.

@Glen thanks for your advise on concatenation,however can you explain me as below the match(1, and the ,(,0),0))

However I am still

why the concatenated array does not work, I have used it so far on three columns with no issue, but for the first time on 4 th columns it did not work .

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**shukla.ankur281190** · 12-27-2017, 06:42 AM

Originally Posted by JEAN1972

However I am still

why the concatenated array does not work, I have used it so far on three columns with no issue, but for the first time on 4 th columns it did not work .

It is just because asterisk *, Match function become confuse to match exact lookup value while matching with condition = couldn't get confuse. Asterisk is wild card character in every programming language as well as in excel too.

**Glenn Kennedy** · 12-27-2017, 06:48 AM

I'm not 100% sure why it is failing... but i suspect that it is because the order of the pack size descriptions is confusing Excel.

The basic formula BSB used:

=INDEX(range,MATCH(1,(range1=crieterion1)*(range2=crieterion2)*(range3=crieterion3),0)) is an array formula where:

red=TRUE
blue=exact match

can be made into a non-array formula by wrapping the criteria in another INDEX like this: INDEX(criteria,0) so the final version became:
=INDEX(range,MATCH(1,INDEX((range1=crieterion1)*(range2=crieterion2)*(range3=crieterion3),0),0))

**Glenn Kennedy** · 12-27-2017, 06:49 AM

Thank you shukla!!

**shukla.ankur281190** · 12-27-2017, 06:55 AM

Originally Posted by Glenn Kennedy

Thank you shukla!!

Thank you Mr. Glenn for added reputation

**Glenn Kennedy** · 12-27-2017, 07:00 AM

Here's another non-array alternative...

=LOOKUP(1,1/(summary!$A$2:$A$13=calculation!C2)/(summary!$B$2:$B$13=calculation!D2)/(summary!$C$2:$C$13=calculation!E2)/(summary!$D$2:$D$13=calculation!F2),summary!$F$2:$F$13)

**JEAN1972** · 12-27-2017, 07:02 AM

To All of you guys

Thank you for your assistance and solution provided

**shukla.ankur281190** · 12-27-2017, 07:09 AM

Originally Posted by JEAN1972

@Glen thanks for your advise on concatenation,however can you explain me as below the match(1, and the ,(,0),0))

However I am still

why the concatenated array does not work, I have used it so far on three columns with no issue, but for the first time on 4 th columns it did not work .

Here you can use concatenated

Formula:

Please Login or Register  to view this content.

array formula index and match not delivering expected results for certain rows

LinkBack

Thread Tools

Rate This Thread

Display

array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Re: array formula index and match not delivering expected results for certain rows

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] INDEX MATCH formula not returning expected result

Sum of multiple Index/Match results using array formula

[SOLVED] Index/Match Formula is not updating as expected

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

In an INDEX(/MATCH( array formula need to go down X number of rows until a term is found

Index match function without array formula for multiple rows.

[SOLVED] Not getting expected results from index match match

Bookmarks

Bookmarks

Posting Permissions