How to get a number from sets of pairs in Excel

Question

In Excel, I want to get the corresponding number for a given value in a set of numbers.
Given values are in disorder.
Example:

1 | 21
2 | 34
3 | 15
4 | 47
5 | 29

Here the minimum value is 15 & max is 47,

I want get the numbers corresponding for 15 & 47, that is 3 & 4.

Note: Column 2 values are not in any order..

What Excel formula should I be using?

Answer 1

You haven't said whether you values in column 2 are unique. You can find the first occurences of the minimum and maximum in column 2 (B) and retrieve the corresponding values in column 1 (A) with:

=INDEX(A1:A5,MATCH(MIN(B1:B5),B1:B5,0))
=INDEX(A1:A5,MATCH(MAX(B1:B5),B1:B5,0))

Answer 2

One method you could use (probably not the best) is to make 2 extra columns. One contains a formula that picks out the maximum, and one that picks out the minimum.

You can use the =IF() function to determine if a value is the maximum or the minimum.

Take this example:

The column C has:

=IF(B1=MAX($B$1:$B$5),A1,"")

and the column D has:

=IF(B1=MIN($B$1:$B$5),A1,"")

copied down the rows. That reveals that C4 is the maximum (4) and D3 is the minimum (3).

A quick summing of these columns means you can put them in another cell and not care which of the rows has the value in it.

B7 has:

=SUM(C1:C5)

and B8 has:

=SUM(D1:D5)

As I say, probably not the best way of doing it, but it works (sort of).