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]
 
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))),"")
@Aladin Akyurek I know I keep saying it - but thank you!

Is there anyway you could teach me the very top level of what we have done here? I'd love to give it a go myself in the future.

Cheers
Ryan
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
@Aladin Akyurek I know I keep saying it - but thank you!

You are welcome. by the way, the SUM formula I proposed in this thread answers the question you posed in your thread.

Is there anyway you could teach me the very top level of what we have done here? I'd love to give it a go myself in the future.

Cheers
Ryan

Here is a link which might help: https://www.mrexcel.com/forum/excel...uplicates-using-sum-if-frequency-match-4.html This contains a multiconditonal formula whch constructs a unique list and an explanation of the FREQUENCY bit works.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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