Max function with lookup

amna77

Active Member
Joined
May 9, 2002
Messages
251
Hi, I am using the max formula like max(B1:B10) to get the highest value.
but I want to look into column C as well. In Column C I have numbers, like
Column B Column C
12/05 1
12/05 3
12/9 3
12/6 2

now I want to get the max value from column B, but my look up number is 3. so basically chek out the value in column C, like how many number 3 I have and then give me max value for number 3 from column B.
Like my answer will be 12/9
Any help?
thanks in advance
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

amna77

Active Member
Joined
May 9, 2002
Messages
251
Columns data was kind of messed up, so here it is again.

ColumnB------ Column C
12/05----------- 1
12/05 -----------3
12/9 ------------3
12/6 ------------2
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi,

One possibility is with an array formula. The difference with one of these is that you enter it with Ctrl - Shift - Enter, not just enter. For example:-
Book1
BCDEF
1ColumnBColumnC
212/05/20021Formulahere>>>12/09/2002
312/05/20023
412/09/20023
512/09/20022
Sheet1
 

amna77

Active Member
Joined
May 9, 2002
Messages
251
That example I gave you its dummy, in the real data my sheet is so huge. so I always have to go my max date.

Thanks for array function, but that Each time I have press Ctrl, Shift and enter keys to update.
Is there any different way to do it? I mean after pluging the formula,then I don't have to press any keys for update.

Thanks for all help
Thanks
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
You don't have to press Ctrl shift and enter each time. It will update in the same way as any other normal formula. Try it...
 

Forum statistics

Threads
1,144,741
Messages
5,726,014
Members
422,653
Latest member
mntsiki

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
Top