Display cell adjacent to MAX value – columns not rows

acowl

New Member
Joined
Jan 19, 2018
Messages
8
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!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Welcome to the Board!

Try this formula:
Code:
=INDEX(I6:Q6,MATCH(MAX(H6,J6,L6,N6,P6),H6:Q6))
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Yes, I can see that image. I recreated your scenario, and it looks like a minor change needs to be made:

Try this:
Code:
=INDEX(I6:Q6,MATCH(MAX(H6,J6,L6,N6,P6),H6:Q6[COLOR=#ff0000],0[/COLOR]))
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You could wrap it in an IFERROR function, telling it to return nothing when an error results, i.e.
Code:
=[COLOR=#ff0000]IFERROR([/COLOR]INDEX(I6:Q6,MATCH(MAX(H6,J6,L6,N6,P6),H6:Q6,0))[COLOR=#ff0000],"")[/COLOR]
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,315
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top