Minimum Value & Cell Cross Referencing

[Johnny]

Hope someone can help: I’ve searched but can’t find a similar questio
so apologies in advance for repetition. This seems REALLY simple bu
I’m failing miserably!

Basically I’m designing a spreadsheet to track the performance of
customer’s network against a service level agreement. I’m trialling i
with 50 sites although it’ll be over 500 in total…. In column
(A2:A52) the names of the sites are listed. In column D (D252) th
site availability is listed.

I can find the lowest number fine by using =MIN(D252) but can I ad
something into a cell which will report the site from column A that th
lowest number corresponds to?

At a very basic level it’ll look something like this (but with straigh
columns!):

¦ A ¦ D ¦
1¦ Site ¦ Availability ¦
2¦ London ¦ 99.50 ¦
3¦ Paris ¦ 99.80 ¦
4¦ New York ¦ 99.90 ¦
5¦ Lowest Avail ¦ 99.50 ¦ - Calculated usin
=MIN(D24)
6¦ Lowest Site ¦ London ¦ - What would I use to ge
this automatically?

Any help would be appreciated

 

[Vasant Nanavati]

Try:

=INDEX(A2:A52,MATCH(MIN(D252),D252,0))

 

[Biff]

Hi!

=INDEX(A2:A52,MATCH(MIN(D252),D252,0))

Is there a possibility of duplicate minimums?

The above formula will always find the first instance.

Biff

 

[Aladin Akyurek]

Let A2:A6 house the sites and D26 their availability scores.

E2, copied down:

=RANK(D2,$D$2:$D$6,1)+COUNTIF($D$22,D2)-1

F2: 1

which means lowest one.

F3:

=MAX(IF(INDEX(D26,MATCH(F2,E2:E6,0))=D26,E2:E6))-F2

which must be confirmed with control+shift+enter, not just with enter.

G2, copied down:

=IF(ROW()-ROW($G$2)+1<=$F$2+$F$3,INDEX($A$2:$A$6,MATCH(ROW()-ROW($G$2)+1,$E$2:$E$6,0)),"")

The foregoing would yield the list:

London
Athens

when A2:A6 houses:

London
Paris
New York
Istanbul
Athens

with as availability scores in D26...

99.5
99.8
99.9
99.67
99.5