Index/Match Formula with Multiple Criteria using Arrays

Z

Zmaster

New member
Joined
Jul 9, 2016
Messages
7
Reaction score
0
Points
0
Hi there

I am a new member to this form and not too sure if there are any formalities before I post any queries, so my apologies in advance.

I have got an issue using an index/match formula to identify the largest 2 sales variances per category. Please refer to my screenshot attached. It seems as though the large formula is looking up the largest number for the entire range, rather than the largest number for pens only or for pencils only or for rulers only. Could somebody please assist with this? Thanks a million.

 

Attachments

  • 2016-07-08 05-51-57 PM.jpg
    2016-07-08 05-51-57 PM.jpg
    98.7 KB · Views: 50
attach an xls* file; pictures are all well and good but why make life difficult for helpers? Go Advanced, Manage Attachments.

Formalities? Yes, new users should acquaint themselves with the rules before first posting. 'Apologies in advance' is a bit of a lame get-out clause that people use which essentially says 'I can't be arsed to read the rules'. It is your duty, if you want free help, to do the necessary minimum.
(My pet hate is cross-posting without providing links; see http://www.excelguru.ca/content.php?184)
 
Thank you very much for enlightening me. I have gone through the cross-posting article and will implement direction.

Re cross-posting, I have posted my query on one other website as a comment to a post, to which I did not seem to receive a concrete conclusion, whereafter I posted to this forum. The query is available in the deskbright .com website
(I cannot post the link because my post count is less than 5 hence I am restricted from posting links.
moderator edit: https://www.deskbright.com/excel/index-match-multiple-criteria/
I have also attached the excel file to make things easier as per your suggestion.

Thanks once again
 

Attachments

  • Index Match Array workbook.xlsx
    12.7 KB · Views: 42
Last edited by a moderator:
Perhaps something along yhe lines of =INDEX($C$4:$C$29;MATCH(LARGE(($B$4:$B$29="pen")*($G$4:$G$29);2);$G$4:$G$29;0)) entered as an array formula?

(replace semi colons with commas if needed)
 
Your LARGE formulae are always finding 65 and 38 regardless of Item, then it tries to match Pencil65 (for example) and there isn't one.
So all I've done is minimally adjust your formulae in column J (column L shows only the important bit that I've changed).

I've also included a pivot table which took less than a minute to set up showing (almost) the same result. The formula approach only shows the first match it finds and not the equal second at all.
 

Attachments

  • ExcelGuru6350Index Match Array workbook.xlsx
    16.8 KB · Views: 40
return data if match duplicate criteria

Another way
Try in cell J5 ARRAY formula (copy down)
Code: 
=INDEX($C$4:$C$29,MATCH(TRIM(SUBSTITUTE(SUBSTITUTE(I5,"1",""),"2",""))&LARGE(IF($B$4:$B$29=TRIM(SUBSTITUTE(SUBSTITUTE(I5,"1",""),"2","")),$G$4:$G$29),RIGHT(I5,1)),$B$4:$B$29&$G$4:$G$29,0))
 

Attachments

  • zmaster-navic.xlsx
    111.7 KB · Views: 24
Last edited:
Thank you everybody. I will try the solutions and post my success (or lack thereof) in the next few days.
 
Thank you everyone for your input. All of your solutions work well. The only other concern I now have is for “pens”, Denmark and Italy both have the 2[SUP]nd[/SUP] largest variance, which is 25. I am not concerned whether Denmark or Italy appears 2[SUP]nd[/SUP] or 3[SUP]rd[/SUP], but what Excel seems to be doing is searching for the largest variances from the top, hence Denmark appears as both the 2[SUP]nd[/SUP] and 3[SUP]rd[/SUP] largest variance, and Italy does not feature at all. Is there any way around this issue? The formula returns Austria as the 4[SUP]th[/SUP] largest variance for “pens” which is correct.

 
Rank value if match two criteria

Zmaster said:
...The only other concern I now have is for “pens”, Denmark and Italy both have the 2[SUP]nd[/SUP] largest variance, which is 25. I am not concerned whether Denmark or Italy appears 2[SUP]nd[/SUP] or 3[SUP]rd[/SUP],....
Click to expand...
How to Excel to decide which value to return when the same?
You need to create an additional condition.


Zmaster said:
T.Is there any way around this issue?.
Click to expand...
Try to see my example. There is a column "helper" which serves to be extracted maximum value for the given criteria.
There you can see a few examples. The following formulas I created (some formula from colleagues I have edited).You choose if you have fits.

column 'helper'
Code: 
=VALUE(G4&COUNTIF(G$4:G4;G4))
1st (Array formula, copy blockcells down)
Code: 
=INDEX($C$4:$C$29;MATCH(LARGE(($B$4:$B$29="pen")*($H$4:$H$29);ROW(A1));$H$4:$H$29;0))
2nd (Array formula, copy block cells down)
Code: 
=INDEX($C$4:$C$29;MATCH("Pen"&LARGE(IF($B$4:$B$29="Pen";$H$4:$H$29);ROW(A1));$B$4:$B$29&$H$4:$H$29;0))
3rd (Array formula, copy down)
Code: 
=INDEX($C$4:$C$29;MATCH(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J4;1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;"");0;""))&LARGE(IF($B$4:$B$29=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J4;1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;"");0;""));$H$4:$H$29);RIGHT(J4;1));$B$4:$B$29&$H$4:$H$29;0))
4th (Array formula, copy down)
Code: 
=INDEX($C$4:$C$29;MATCH(LARGE(($B$4:$B$29=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J4;1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;"");0;"")))*($H$4:$H$29);RIGHT(J4;1));$H$4:$H$29;0))
5th (shorter Array formula, copy down)
Code: 
=INDEX($C$4:$C$29;MATCH(TRIM(IFERROR(LEFT(J4;FIND(" ";J4)-1);J4))&LARGE(IF($B$4:$B$29=TRIM(IFERROR(LEFT(J4;FIND(" ";J4)-1);J4));$H$4:$H$29);RIGHT(J4;1));$B$4:$B$29&$H$4:$H$29;0))
 

Attachments

  • zmaster-navic2.xlsx
    115.7 KB · Views: 26
You must log in or register to reply here.
Back
Top