VBA: Returning a max value after looking up multiple values

mkc

New Member
Joined
Apr 10, 2009
Messages
2
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!
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

smiths87

Board Regular
Joined
Aug 7, 2006
Messages
106
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

New Member
Joined
Apr 10, 2009
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top