0

I'm trying to use Excel to return the highest & lowest date option so I can return the true effective/expiration dates without multiple lines. Example of ranges/rates:

Rate State. Effective Expired
$5.00 AZ 01/01/2022 1/31/2022
$5.00 AZ 02/01/2022 2/30/2022
$6.00 AZ 03/01/2022 3/31/2022
$6.00 WI 01/01/2022 01/31/2022
$6.00 WI 02/01/2022 02/30/2022

In the example above, AZ's rate of $5.00 stays the same from 01/01/2022-02/30/2022, then changes to $6.00, so i'd like a simplified return to look like this:

Rate State Effective Expired
$5.00 AZ 01/01/2022 02/30/2022
$6.00 AZ 03/01/2022 03/31/2022
$6.00 WI 01/01/2022 02/30/2022

I tried using an XLOOKUP, but when the XLOOKUP finds multiple matches, it only returns the first option. I've been trying to find a way to make it return the MIN or MAX option and this doesn't work.

I also tried an INDEX/MATCH with similar results.

In both situations the plan was to create duplicate lines then delete the excess ones on the other end.

Jac
  • 1

1 Answers1

1

Screenshot illustrating proposed formula

The formula in F2 is

=LET(rates,A2:A6,states,B2:B6,effdates,C2:C6,expdates,D2:D6,dat,UNIQUE(CHOOSE({1,2},rates,states)),one,INDEX(dat,,1),two,INDEX(dat,,2),CHOOSE({1,1,2,3},dat,MINIFS(effdates,states,two,rates,one),MAXIFS(expdates,states,two,rates,one)))

This produces the desired results for your limited dataset, but is very much dependent on the content and layout of your data, e.g. if the last line of your sample were for a $5 rate for AZ from 1st to 30th April then the summary result would yield an expiry date of April 30th for the $5 AZ rate, despite there being no option data for March(!).

Spectral Instance
  • 1,971
  • 2
  • 3
  • 6