Lookup/Array Formula

ryansm05

Board Regular
Joined
Sep 14, 2016
Messages
148
Office Version
  1. 365
Platform
  1. Windows
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

mcdk.jpg
[/URL][/IMG]
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Try:

ABCDEFGHI
1
2
3AprMayJuneJulyAugustSeptember
4Q2Q2Q2Q3Q3Q3Total
5100Burger King5050
6101KFC6080100240
7102McDonalds504090
8103Subway102030
9104Wimpy52.57.5
10
11
12
13
14
15ClientsQ3
16101KFC100
17102McDonalds40
18

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
I5=SUM(C5:H5)
B16=IF(A16<>"",VLOOKUP(A16,$A$5:$B$9,2,0),"")
C16=IF(A16="","",SUMPRODUCT($C$5:$H$9*($C$4:$H$4=$C$15)*($A$5:$A$9=A16)))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
A16{=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)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0

Book1
ABCDEFGHI
3AprMayJuneJulyAugustSeptember
4Q2Q2Q2Q3Q3Q3Total
5100Burger King5050
6101KFC6080100240
7102McDonalds504090
8103Subway102030
9104Wimpy52.57.5
10
11
12
13
14ClientsQ3
15101KFC100
16102McDonalds40
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))))
 
Upvote 0
@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
 
Last edited:
Upvote 0
@ 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


Error.jpg
[/URL][/IMG]

Error1.jpg
[/URL][/IMG]
 
Upvote 0
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...
 
Last edited:
Upvote 0
@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
FINAL.jpg
[/URL][/IMG]
 
Upvote 0
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))),"")
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top