Need help with VLookup & MAX formula combination

[Raul Aponte]

Hi,

This is my first post here, so forgive me for any lack of information and terminology needed to help me out with my peculiar case.

I need to create a formula where a the max price of a range of item numbers is returned. I'm attaching a file with an example of what I'd like to happen. I've been trying and trying with no success; combining numerous formulas and parameters.

I appreciate any help offered!

 

[bosco_yip]

Try,

In D4, formula copy down :

=LOOKUP(2,1/(LEFT(B4,8)=LEFT(Sheet2!B$2:B$35,8)),Sheet2!G$2:G$35)

Regards

 

[Raul Aponte]

Thank you! That actually helps a lot. I noticed that the formula uses the MAX formula I created in Sheet2. Is there a way to use the previous column? Sheet2 is a list that's updated periodically and changes frequently. I'd hate to make a MAX formula every time I update the sheet.