MAX value across multiple sheets + VLOOKUP (maybe) with multiple results

Hi all,

I've been googling this and trying to crack it for ages and could use some help :)

sheet 1
100 5
100 2
100 3
200 4

sheet 2
100 6
100 4
200 2
200 1

The formula would find the max value in col B when col A=100.

This should be simple? It's essentially this problem: http://www.mrexcel.com/forum/excel-q...iple-hits.html but the suggested solution didn't work across multiple sheets for me: =MAX(IF(sheet1!A1:A4=100,sheet2!B1:B4))

Thank you very much!

Hi,

Please find attached excel file along with the formula max values from multiple sheets.

Imran.