 # Vlookup max number

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• 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

## Files

I've found that the problem usually lies inbetween the keyboard and the chair

• 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'

I've found that the problem usually lies inbetween the keyboard and the chair

• Re: Vlookup max number

Nope, should I be?

I've found that the problem usually lies inbetween the keyboard and the chair

• 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

I've found that the problem usually lies inbetween the keyboard and the chair

• 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