Lookup a month from a weekly date range and return the minimum value in that month?

**as9178** · 01-17-2020, 11:18 AM

Hi all,

My conundrum is this

I have in columns numerous dates (the dates in reality stretch from Nov-19 to Jul-21) with values below. This is on one tab in excel

16/11/19 - 23/11/19 - 30/11/19 - 7/12/19 - 14/12/19 - 21/12/19 -

there are then then values below each date

23.42 - 24.56 - 23.45 - - 24.43 - -

On the next tab I have the following columns with months (again this will go from Nov-19 to Jul-21

Nov-19 - Dec-19

This is where I need the excel magic to happen

I need excel to look on the previous tab and provide the lowest value which is not zero

So the answer should look like

Nov-19 - Dec-19

23.42 - 24.43

Does that make sense and can anyone help please?

**Pepe Le Mokko** · 01-17-2020, 12:04 PM

Administrative note

Welcome to the forum

missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

**as9178** · 01-17-2020, 04:45 PM

File now atttached

**Ace_XL** · 01-17-2020, 05:29 PM

In C10 try this array formula

=MIN(IF(TEXT($C$3:$L$3,"mmmyyyy")=TEXT(C$9,"mmmyyyy"),IF($C4:$L4<>"",$C4:$L4)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy down and across

**as9178** · 01-17-2020, 05:37 PM

Thats incredible, thankyou

Lookup a month from a weekly date range and return the minimum value in that month?

LinkBack

Thread Tools

Rate This Thread

Display

Lookup a month from a weekly date range and return the minimum value in that month?

Re: Lookup a month from a weekly date range and return the minimum value in that month?

Re: Lookup a month from a weekly date range and return the minimum value in that month?

Re: Lookup a month from a weekly date range and return the minimum value in that month?

Re: Lookup a month from a weekly date range and return the minimum value in that month?

Thread Information

Users Browsing this Thread

Similar Threads

If month and year of a date in a range of dates matches a specific date, return true

[SOLVED] Is there a way to insert all dates for a month weekly when user inputs Month and a Year?

[SOLVED] Formula to return End OF Month date with non-calendar month lengths.

[SOLVED] Vlookup date range to return month

[SOLVED] Create Fiscal Year - Lookup Date in Fiscal Month Date Range and Return EOM Date

[SOLVED] lookup date within date range grid to return fiscal month value

Formula to return day based on month end date & day number of month

Bookmarks

Bookmarks

Posting Permissions