I am hoping someone can suggest a code for the attached. I need something like a Vlookup in sheet1 that will find the maximum number for each month when looking at sheet2. I.e Shhet1 Jan 11 would show Apples 436, Oranges 843 etc, etc.Thanks
Vlookup max number
-
-
Re: Vlookup max number
Put this CSE formula in Sheet1B3 and drag down and right
=MAX((MONTH(Sheet2!$A$3:$A$44)=MONTH($A3))*(Sheet2!B$3:B$44))
-
Re: Vlookup max number
Thanks, but this is only giving me numbers in rows 3 and 18 on sheet1 - and these aren't the correct figures. All other cells are showing '0'
-
Re: Vlookup max number
Are you entering the formula with Ctrl-Shift-Enter (Cmd+Return for Mac)?
-
Re: Vlookup max number
Nope, should I be?
-
-
Re: Vlookup max number
Used Ctrl-Shift-Enter and it works for the first year, then starts to repeat the '11 figures if I stretch to '12 year. I have amended the formula to cover the increase in rows to account, but still only repeatsThanks
-
Re: Vlookup max number
I forgot the year issue
=MAX((YEAR(Sheet2!$A$3:$A$44)+MONTH(Sheet2!$A$3:$A$44)/12=(YEAR($A3)+MONTH($A3)/12))*(Sheet2!B$3:B$44))
-
Re: Vlookup max number
Brilliant!!!Many thanks fior your help
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!