VBA: Returning a max value after looking up multiple values

[mkc]

Hi. I'm trying to do some VBA programming for a project at work. Because I'm new to VBA and have very limited programming experience (everything I know, I've learned in the past couple days), I need some help.

I'm trying to write a function that will look up two different values in two different columns, and then return the maximum value of all the matches from a third column.

For example...

Using the values shown below, I want to look up all instances where the first column contains "Cat" and the second column contains "Bee", and return the maximum value from the third column which would be "6".


Cat Bird 8
Cat Bee 6 <===
Dog Bird 3
Cat Bee 2 <===

I'm currently using Office 2002.
Any help would be appreciated. Thanks!

 

[smiths87]

You could use the Large Function that Excel has. Try the below formula:

=LARGE(($A$1:$A$4="Cat")*($B$1:$B$4="Bee")*($C$1:$C$4),1)

(Confirm it with Control+Shift+Enter)

 

[mkc]

Thanks for the Large Function advice, but I don't think it's going to work for what I want. The example I gave is actually much simpler than what I will actually need for the finished product. I was just hoping to get some help on the VBA code as a place to start.