Lookup/Array Formula

[ryansm05]

Hi,

Would anyone be able to help with the following? I've attached a screenshot for ease.

My aim is to return the project codes 101 & 102 - this is because they have values against them in the three Q3 fields.

Please note that I also want to avoid duplication ... so if there was two lines with 101 McDonalds ... I'd only want to return this once.

Thanks in advance!
Ryan

[/URL][/IMG]

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I
Apr	May	June	July	August	September
Q2	Q2	Q2	Q3	Q3	Q3	Total
Burger King
KFC
McDonalds
Subway
Wimpy
Clients	Q3
KFC
McDonalds

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]101[/TD]

[TD="align: right"][/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]240[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]102[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]90[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]103[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]104[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7.5[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]101[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]102[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I5[/TH]
[TD="align: left"]=SUM(C5:H5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B16[/TH]
[TD="align: left"]=IF(A16<>"",VLOOKUP(A16,$A$5:$B$9,2,0),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C16[/TH]
[TD="align: left"]=IF(A16="","",SUMPRODUCT($C$5:$H$9*($C$4:$H$4=$C$15)*($A$5:$A$9=A16)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A16[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$5:$A$9,SMALL(IF(COUNTIF($A$15:$A15,$A$5:$A$9)=0,IF(MMULT($C$5:$H$9*($C$4:$H$4=$C$15),TRANSPOSE(COLUMN($C$4:$H$4))),ROW($A$5:$A$9)-ROW($A$5)+1)),1)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I
3			Apr	May	June	July	August	September
4			Q2	Q2	Q2	Q3	Q3	Q3	Total
5	100	Burger King	50						50
6	101	KFC		60	80	100			240
7	102	McDonalds			50			40	90
8	103	Subway	10	20					30
9	104	Wimpy		5	2.5				7.5
10
11
12
13
14		Clients	Q3
15	101	KFC	100
16	102	McDonalds	40
17
Sheet1

In A15 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$5:$A$9,SMALL(IF(FREQUENCY(IF($C$4:$H$4=$C$14,IF(ISNUMBER($C$5:$H$9),MATCH($A$5:$A$9,$A$5:$A$9,0))),ROW($A$5:$A$9)-ROW($A$5)+1),ROW($A$5:$A$9)-ROW($A$5)+1),ROWS($A$15:A15))),"")

In B15 just enter and copy down:

=IF($A15="","",VLOOKUP($A15,$A$5:$B$9,2,0))

In C15 control+shift+enter, not just enter...

Either:

=IF($A15="","",SUM(IF($C$4:$H$4=C$14,IF($A$5:$A$9=$A15,$C$5:$H$9))))

Or:

=IF($A15="","",MAX(IF($C$4:$H$4=C$14,IF($A$5:$A$9=$A15,$C$5:$H$9))))

 

[ryansm05]

@Aladin Akyurek / @Eric W

Hi guys - I've tried both approaches and obviously completed the array formulas with ctrl+shift+enter. It just fails to return anything in column A (and this appears to control the logic).

The formula looks hugely impressive and it must be close so I thank you both.

Did you manage to get this to work on your own excel (and perhaps I'm doing something wrong?)

Thanks again
Ryan

 

[Aladin Akyurek]

@ ryansm05

Control+shift+enter >> Press down the control and the shift keys at the same time while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

See the implementation: https://www.dropbox.com/s/fkvanmeentd5gvs/ryansm05 unique list etc.xlsx?dl=0

 

[ryansm05]

@ ryansm05

Control+shift+enter >> Press down the control and the shift keys at the same time while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

See the implementation: https://www.dropbox.com/s/fkvanmeentd5gvs/ryansm05 unique list etc.xlsx?dl=0

@Aladin Akyurek - Please see below screenshots. I've executed the array correctly but it does not return anything. I've also had a play with where cells were fixed etc + tried adjusting the lookup, but still no joy..

Do you have any ideas?

Cheers

[/URL][/IMG]

[/URL][/IMG]

 

[Aladin Akyurek]

Did you examine the file I provided?

 

[ryansm05]

Did you examine the file I provided?

That's amazing @Aladin Akyurek

I've edited this as I didn't need my last question answered.

I jus want to say thank you for your help, and ask how you accumulated this knowledge? Would you be able to briefly run me through the logic here. I'm pretty good with general formulas but arrays are something new to me.

i'd love to be able to have a go myself and offer something back to the community as opposed to pestering you guys...

 

[ryansm05]

@Aladin Akyurek


Final question Aladin ...

If I wanted to add a third criteria - exactly the same as before, but to only include UK stores (in column C) ... what would I need to do to amend the formula?

If you can help me here I'll be eternally grateful.

Thanks
Ryan

[/URL][/IMG]

 

[Aladin Akyurek]

I hope this isn't confusing...

In A15 control+shift+enter and copy down:

=IFERROR(INDEX($A$5:$A$9,SMALL(IF(FREQUENCY(IF($C$5:$C$9="UK",IF($D$4:$I$4=$C$14,IF(ISNUMBER($D$5:$I$9),MATCH($A$5:$A$9,$A$5:$A$9,0)))),ROW($A$5:$A$9)-ROW($A$5)+1),ROW($A$5:$A$9)-ROW($A$5)+1),ROWS($A$15:A15))),"")