 # Vlookup max number

• 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