What function to use

Hi

I would like to search across a number of rows in a worksheet and then match against two columns.

e.g.

Column B Column C

test 10


I would like the function to match against all rows that match against "test" in Column B and then work out which cell in Column C has the highest value

I have looked in excel help but it I cannot find anything that shows me an example of what I am trying to do

I would appreciate any help on this

Thanks

Originally Posted by noviceexcel

Hi

I would like to search across a number of rows in a worksheet and then match against two columns.

e.g.

Column B Column C

test 10


I would like the function to match against all rows that match against "test" in Column B and then work out which cell in Column C has the highest value

I have looked in excel help but it I cannot find anything that shows me an example of what I am trying to do

I would appreciate any help on this

Thanks

=MAX(IF(A2:A9="test",B2:B9))

ctrl+shift+enter (not just enter)

Thanks very much for the answer. That works great.

How can i update this formula to ignore any cells that are blank

Thanks

Thanks

I did add it as an array using ctrl shift & enter but i want the formula to ignore blank cells. Currently if the cells are blank it returns a value of 0. I would like the formula to be ignored if the cells are blank

Thanks again

what does ctrl-shift-enter do?

Been playing with it for the past half hour and figured out what it does. Thanks for the links though! I just bookmarked them!