Excel Find Maximum Value for a matched value 1

[sdiverdan]

I have a spreadsheet containing 45 different rows representing network lines, a column has line speeds (128,256, 512..) and another column has the maximum Kbps for that particular line for a particular day. I'm trying to retreive into a cell the maximum Kbps for a line speed. I know the large function gives me the maximum for a column. I've tried vlookup, match, sumif,countif and arrays however to no success. How do I find the largest value for all the 128 lines? I'm really stumped. %-)


Dan

 

[KenWright]

With your line speeds in Col A A1:A30 and the actual speeds in Col B B1:B30, and the speed you wish to query in D1, in any other cell use:-

=MAX(IF(A1:A30=$D$1,B1:B30))

Array entered using CTRL+SHIFT+ENTER

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[sdiverdan]

Thanks Ken, here's a star, that's exactly what I was looking for! thanks...


Dan

 

[KenWright]

My pleasure, and thanks for the feedback.

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------