Lookup in Excel

  • Thread starter Thread starter JD
  • Start date Start date
J

JD

I have a spreadsheet with the following:

Cities in Column B
Amounts in Col C

It is sorted by City. in cell G10 I have a formula that
returns the minimum number in the list. In cell H10, I
would like to do a lookup to show what city is the
minimum. When I try to do V lookup it errors out because
I have to look in the first column. This should be simple-
please help.
 
JD,

Use (assuming your cities / amounts are in B1:C15)
=INDEX(B1:B15,MATCH(G10,C1:C15,0))

If you have European regional setting replace the , with ;

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Let B2:B10 house:

{"City","Amount";"Den
Haag",40;"Utrecht",60;"Groningen",40;"Rotterdam",25;"Leiden",35;"Delft",25;"
Eindhoven",60;"Nijmegen",32}

In D3 enter & copy down:

=RANK(C3,$C$3:$C$10,1)+COUNTIF($C$3:C3,C3)-1

In F2 enter:

=MIN(C3:C10)

In F3 control+shift+enter, that is, confirm the formula with
control+shift+enter instead of just with enter:

=MAX(IF(INDEX(C3:C10,MATCH(F4,D3:D10,0))=C3:C10,D3:D10))-F4

In F4 enter: 1 (N of Top N with lowest amount)

In F6 enter & copy down:

=IF(ROW()-ROW(F$6)+1<=$F$3+$F$4,INDEX($B$3:$B$10,MATCH(ROW()-ROW(F$6)+1,$D$3
:$D$10,0)),"")

This is what you get to see in F from F1 on:

{"Min";
25;
1;
1;
"Min Cities";
"Rotterdam";
"Delft";
"";
"";
""}

Thus, in this sample, 2 cities, not one, are associated with the minimum
amount.
 
Back
Top