Excel Array formula Index/Match based on Max value

In attached, top table should match the values of bottom table.

I know I'm mostly right, but can't crack it.
QueryTable.xlsm
newparadigmzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
I assume you can get the correct dates in columns O and P using your existing O18 formula copied across to P18 and down both columns. Once you have those try this formula in N18

=INDEX(otr!$A$3:$I$42,MATCH(1,($D$3:$D$42=$O18)*($A$3:$A$42=$M18),0),MATCH(otr!N$17,otr!$A$1:$I$1,0))

confirmed with CTRL+SHIFT+ENTER and copied down column

regards, barry
0
barry houdiniCommented:
.......no actually I see you won't get the right results for column P, so you need this version for P18 copied down

=INDEX(otr!$A$3:$I$42,MATCH(MAX(IF($A$3:$A$42=$M18,$E$3:$E$42)),$E$3:$E$42,0),MATCH(otr!P$17,otr!$A$1:$I$1,0))

Note that if you take the max date for "Issue" and the max date for "Maturity" theoretically they might not belong to the same row. My suggested N18 formula just matches the max Issue date - if you want you can match both with this version

=INDEX(otr!$A$3:$I$42,MATCH(1,($D$3:$D$42=$O18)*($E$3:$E$42=$P18)*($A$3:$A$42=$M18),0),MATCH(otr!N$17,otr!$A$1:$I$1,0))

In this sample it gives you the same results as the previously suggested N18 formula, but depending on your data it may not do so (and may even return #N/A if there is no matching row).

regards, barry
0
redmondbCommented:
newparadigmz,

The attached has two versions. The first (yellow) uses a helper column ("Row") to find the relevant row...
=OFFSET($A$1,$Q18-1,MATCH(N$17,$A$1:$I$1,0)-1)

Open in new window

...and the helper...
{=MIN(IF($A$3:$A$42&":"&$D$3:$D$42=$M18&":"&MAX(IF($A$3:$A$42=$M18,$D$3:$D$42)),ROW($A$3:$A$42),9^10))}

Open in new window

The second version (red) doesn't use a helper...
=OFFSET($A$1,MIN(IF($A$3:$A$42&":"&$D$3:$D$42=$M38&":"&MAX(IF($A$3:$A$42=$M38,$D$3:$D$42)),ROW($A$3:$A$42),9^10))-1,MATCH(N$17,$A$1:$I$1,0)-1)

Open in new window

Regards,
Brian.QueryTable-V2.xlsm
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

newparadigmzAuthor Commented:
@barry, you were right on the first count, only based off max of Issue, but you're not using max formula? also, when I apply it to other fields, getting circular reference errors.

@redmond, yours works consistently, but do you think it's possible to just use index/match/max combo, like what I was trying to attempt?
0
redmondbCommented:
newparadigmz,

I'm sorry that mine wasn't what you wanted, but, trust me, it would be a complete waste of your time and mine to work on it while Barry's involved!

Regards,
brian.
0
barry houdiniCommented:
The first formula I suggested for N18 was this one

=INDEX(otr!$A$3:$I$42,MATCH(1,($D$3:$D$42=$O18)*($A$3:$A$42=$M18),0),MATCH(otr!N$17,otr!$A$1:$I$1,0))

It doesn't use MAX but it uses the result from O18 so it is using MAX indirectly because that is used in O18.

Where do you get a circular reference?

Perhaps you can replace O18 with the MAX(IF part, i.e.

=INDEX(otr!$A$3:$I$42,MATCH(1,($D$3:$D$42=MAX(IF($A$3:$A$42=$M18,$D$3:$D$42)))*($A$3:$A$42=$M18),0),MATCH(otr!N$17,otr!$A$1:$I$1,0))

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
newparadimz,
Which version of Excel are you using?

If the answer is Excel 2010 or later, then you can use the AGGREGATE function. It will let you create a formula that has the benefits of Boolean expressions without needing to be array-entered. Here is a formula for P18 that you may copy both across and down:
=AGGREGATE(14,6,INDEX(otr!$A$3:$I$42,,MATCH(P$17,otr!$A$1:$I$1,0))/(otr!$A$3:$A$42=$M18),1)

The first parameter (14) means to find the largest values. The last parameter (1) means to find the first largest value. The combination is equivalent to MAX. The second parameter (6) means to ignore error values (the Boolean expression returns an error value if no match in column A for M18).

Brad
0
newparadigmzAuthor Commented:
@ barry, yes!, last formula works perfect!
The circular reference was because formula should look in the main data table, and copied across, so when I put it in O18, it referenced itself.
While I don't fully understand the Match nesting, I can 'see' that it is acting on the max of Issue date and lookup value.

@ red, yours also works perfect, just less intuitive for me to understand.

@ byundt, thanks, using 2007, but good to know!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.