Index Match w/ 2 Criteria

[lalahaedong_excel]

I am trying to use Index Match based on (2) criterias ( have attached example book).

I am trying to Index the Begin/End Dates

based on

the matched "Max Usage" and the BillMoID

For instance if Max Usage (G) shows a value ( like 67008 )
the i want the Begin Date that matches that value and the CustBillMoID (F) result would be something like Begin Date 09/01/2017 and End Date 10/02/2017

Is this possible or should I use something other than Index Match ?

[FlameRetired]

Try array entering this formula in H2. Then fill down and across column I.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Then array enter this formula in J2 and fill down.

Please Login or Register  to view this content.

[FlameRetired]

Another way. Use the same formula in J2 as above. Then array enter this in H2, fill down and across.

Please Login or Register  to view this content.

[lalahaedong_excel]

I try doing as instructed but its simply not working ...I used the array but its only giving me the 1st date of 09/01/2017.

Thank you

[FlameRetired]

It sounds like you may be committing the whole range at once with CSE. Just commit the 1st row and fill down ... and across for the begin/end dates.

See the attached.

[bebo021999]

Try in H2 then copy down and accross

Please Login or Register  to view this content.

[FlameRetired]

@ Quang,

I like it.

Using the offsets never occurred to me. With that formula in mind how about this?

Please Login or Register  to view this content.

[bebo021999]

@ Quang,

I like it.

Using the offsets never occurred to me. With that formula in mind how about this?

Please Login or Register  to view this content.

Yes. I think SUMIFS here completely replace SUMPRODUCT.
Good catch.
Anyway, waiting for OP confirmation.
I though the real data is not so simple with duplicate begin-end date for specify BillMoID like this.

[lalahaedong_excel]

Works Great after a tweak or two...

Thank you so much