CODEWITHSUNDEEP
excelexcel-formula
Baasoti
Baasoti

Reputation: 37

Pass an intermediate filtered range to INDEX MATCH

I have a table of contracts as below.

   A   |   B   |   C   | 
-------------------------
   X   |   5   |   7   |
   Y   |   2   |   4   |
   Z   |   1   |   6   |
   Z   |   5   |   8   |
   X   |   3   |   5   |

I want to produce a second table.

   A   |   B   |   C   | 
-------------------------
   X   |   3   |   7   |
   Y   |   2   |   4   |
   Z   |   1   |   8   |

This second table takes all the unique values from column A of the first table, then finds the lowest column B value associated with this unique value, and the highest column C value.

Using INDEX MATCH feels like the way to go, except that the first table isn't sorted by columns B or C.

I've found ways of sorting a range with formulas, but not in a way that I can pass to INDEX MATCH.

Any help appreciated.

Upvotes: 0

Views: 136

Answers (3)

user4039065
user4039065

Reputation:

One array formula and two standard psuedo-MINIF/MAXIF formulas.

In F2:H2,

=INDEX(A$2:A$6, MATCH(0, COUNTIFS(F$1:F1, A$2:A$6), 0))   'array formula
=MIN(INDEX(B$2:B$6+(A$2:A$6<>F2)*1E+99, , ))              'MINIFS
=MAX(INDEX(C$2:C$6+(A$2:A$6<>F2)*-1E+99, , ))             'MAXIFS

enter image description here

Upvotes: 1

Olly
Olly

Reputation: 7891

You could use Power Query (Get & Transform Data):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"A"}, {{"B", each List.Min([B]), type number}, {"C", each List.Max([C]), type number}})
in
    #"Grouped Rows"

You could use a Pivot Table:

Field A in ROWS, Field B in VALUES (summarized by Min), Field C in VALUES (summarized by Max)

Upvotes: 3

Samuel Hulla
Samuel Hulla

Reputation: 7099

Like with many Excel questions here, you're trying to reinvent the wheel here for no reason.

Excel already has inbuilt functionality for this. Let me introduce you to the world of Pivot Tables

  1. Select your table Data like so

    enter image description here

  2. Insert Tab -> Pivot Table

    enter image description here]2

  3. Change the Value of Pivot Fields from Sum to Min for Column B, and Max for Column C

    enter image description hereenter image description here

Provides Result as Expected:

enter image description here

Upvotes: 3

Related Questions