INDEX MATCH based on multiple criteria. One is FIND and other is Max date

0
votes

Hoping for some help with a formula. I haven't been able to find an answer for this particular combination of issues.

I want to create an INDEX MATCH lookup based on three criteria:

  1. Normal text lookup
  2. Text lookup possibly within a larger string of text (used FIND for this)
  3. Latest date

I can't figure out how to work in the third point. Here's my formula that captures the first two:

=INDEX($C$2:$C$4,MATCH(1,($A$8=$A$2:$A$4)*(FIND(B8,$B$2:$B$4)>0),0))

See image below. I would want the result in cell C8 to be "Q3" because that row has the later date and fulfills the other two criteria.

Excel Formula Pic

1
excelexcel-formula

1 Answers

0
votes

This can be made simpler with a few changes.

If you change column C to a number, and just use formatting to add the "Q", you can then use excel's MaxIFs function.

So then, assuming the lookup is based on A8 and B8 , we can seek the max 'Quarter?' field ( or date ) where it matches those criterea, and better still, the syntax is much easier to read ( for example, we can find 'B' with a Wildcard. )

=MAXIFS(C1:C4,B1:B4,"*B*",A1:A4,A9)

[edit] - here's the example sheet : http://www.instantexcel.com/downloads/stackexchange/64235227.xlsx

See attached image for a better example. Worksheet example

[edit 2] :: - I got a warning message from Stack Overflow not to reply to the message, so I'm adding it here.

Are saying you're unable to change the data in the sheet (add a column / format / create a synthetic key by concatenation etc?

You could combine a maxifs, to get A - The latest data in column D, where Columns A and B are matched ( CTG and DETAIL ) B - Use the result of (A) - which I assume would NOT be a duplicate/reoccuring value?

I'd use example :

=INDEX($C$1:$C$4,MATCH(MAXIFS(D1:D4,B1:B4,"*"&B9&"*",A1:A4,A9),D1:D4,0))