Array

  • Thread starter Thread starter Tony Cayer
  • Start date Start date
T

Tony Cayer

Hi,

How I can search 0a-m1 in column B to return the highest value in column C
of all rows that are returned from the search of 0A-M1.
The result should be 74898.

sort could be done by A, B or Row number
column A is network device "switch" or a PBX
column B is a location
column C is the phone extension "same location could be have more than 1
extension or a blank field"


Thanks

ex:
A B C
1 VG224 0a-m6 8000
2 G3Si 0a-m1 8915
3 Cisco CM 0a-m7 73000
4 Cisco CM 0a-m1 74898
5 Cisco 0a-m1 0
etc..
 
Try this array formula that must be entered using ctrl+shift+enter. Modify
to suit.
=MAX(IF(B1:B15="a",C1:C15))
 
the result return #VALUE!


ex:
G3Si:A_01A1318 0A-27 8325
G700-MGP2_002V213 0A-27 4242
NQUEVGIDF3B04:2/07 0A-27 10197
SA0A01:Fa1/0/27 0A-27 74130
WSCSW02-3:Fa0/22 0A-27
 
=MAX(IF(B1:B5="0A-27",C1:C5))

Don Guillett said:
Post YOUR formula here. Did you array enter?
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Click to expand...
 
You failed to array enter the formula and to make your ranges absolute for
the different formulae
 
Back
Top