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]

 

[ryansm05]

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

 

[Aladin Akyurek]

@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.