Use set of keywords to extract values from second

Question

I'll try to explain the problem I'm facing best as I can.

A have a set of data that contains multiple duplicates extracted as an excel file. Within this data are "keys" that I want to use to filter out relevant data from another workbook.

I start by removing duplicates from the list of keywords and I think I got this working kind of satisfactory. I then try to extract and calculate the minimum from the values using the following array formula:

=MIN(VLOOKUP(Blad1!D2:D8,Blad2!A3:D9,2))

However, this doesn't work as expected. The value returns the minimum value from the target range, but seems to ignore the provided keywords. Instead it simply finds the minimum value of the entire range.

I am far from a professional when it comes to excel so any suggestions on how this could be done in a more efficient way are welcome.

Here is a link to a sample document.

Answer 1

These array formulas should be what you need.

'MINIF in F2,
=MIN(IF(COUNTIF($D$2:$D$8, Blad2!$A$2:$A$9&""), Blad2!$B$2:$B$9))
'MAXIF in G2
=MAX(IF(COUNTIF($D$2:$D$8, Blad2!$A$2:$A$9&""), Blad2!$C$2:$C$9))
'AVERAGEIF¹ in H2
=AVERAGE(IF(COUNTIF($D$2:$D$8, Blad2!$A$2:$A$9&""), Blad2!$D$2:$D$9))

Array formulas need to be finalized with Ctrl+Shift+Enter↵. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum.

The results are 15, 35 and 23.6.

¹Note that this is NOT the native AVERAGEIF function or AVERAGEIFS function but an array formula. This approach was chosen due to the large number of criteria.