Display cell adjacent to MAX value – columns not rows

[acowl]

Hi, I have alternating columns of Date 1, Amount 1, Date 2, Amount 2, etc. corresponding to a record in column A. I have this setup as Date 1 (H6), Amount 1 (I6), Date 2 (J6), Amount 2 (K6) etc.


I want to be able to see the most recent date and the corresponding figure from that date.

So far I have =MAX(H6,J6,L6,N6,P6) to show the most recent date, but I need another formula to show the value from the adjacent cell to the one returned in the MAX formula.

Any ideas?

Thanks!

 

[Joe4]

Welcome to the Board!

Try this formula:

=INDEX(I6:Q6,MATCH(MAX(H6,J6,L6,N6,P6),H6:Q6))

 

[acowl]

Thanks for the help! I've filled in some dummy numbers and this comes back with a zero value – image linked. Am I doing something wrong somewhere?

https://imgur.com/a/MqITx

 

[Joe4]

Unfortunately, I cannot view your image. My workplace blocks that site (as well as as file sharing site) for security reasons.
There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

If you are not able to post the image here, can you just list out the value in cells H6:Q6?

Just to be clear, the formula I posted will find the maximum date value in H6,J6,L6,N6,P6, and then return the value the the cell just to the right of that.
So, if the max date was in cell L6, it will return the value from M6.

 

[acowl]

Thanks Joe4, the formula should work in that case, so I think it must be my own error somewhere.

Unfortunately, I'm on a mac and the Forum Tools Add-In doesn't seem able to copy an image, and pasting according to the Borders-Copy Paste instructions loses all the structure of the table. The Test section does not seem relevant to my query, unless I'm misunderstanding? The 'Insert Image' function on the forum doesn't seem to do anything either.

Can you follow this link to see the image? https://deadinkbooks.com/wp-<wbr>content/uploads/2018/01/<wbr>Screen-Shot-2018-01-19-at-14.<wbr>00.41.png

 

[Joe4]

Yes, I can see that image. I recreated your scenario, and it looks like a minor change needs to be made:

Try this:

=INDEX(I6:Q6,MATCH(MAX(H6,J6,L6,N6,P6),H6:Q6[COLOR=#ff0000],0[/COLOR]))

 

[acowl]

Yes! Thank you, this works perfectly.

 

[Joe4]

You are welcome!

I forgot that in using the MATCH function, using the default "match_type" argument only works if the data is sorted in ascending order.
So, we need to explicitly set that argument to "0".
See here for an explanation, especially the three options it shows for the "match_type" argument: https://www.techonthenet.com/excel/formulas/match.php

 

[acowl]

Ah – one more question. How could I modify this formula to avoid showing #N/A when no data is available? Would this be using an IF function? Many thanks for your help again.

 

[Joe4]

You could wrap it in an IFERROR function, telling it to return nothing when an error results, i.e.

=[COLOR=#ff0000]IFERROR([/COLOR]INDEX(I6:Q6,MATCH(MAX(H6,J6,L6,N6,P6),H6:Q6,0))[COLOR=#ff0000],"")[/COLOR]