# How to get a number from sets of pairs in Excel

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?

• Welcome to Super User! Please use the "edit" link to give this a title that summarizes your question. Also note the formatting help in the right column while editing. (Like, use a blank line before your example, and indent it using 4 spaces to get the expected formatting.) Thanks! – Arjan Apr 10 '11 at 18:43
• (Also, it seems a bit odd to type your example columns as rows?) – Arjan Apr 10 '11 at 18:54
• Odd, we can see you've been back 50 minutes ago. Please put some time in formatting your question! – Arjan Apr 10 '11 at 19:46
• @Arjan don't worry - I'll do it for him... poor newbies... – Majenko Apr 10 '11 at 21:02
• Kudos, @Matt! (Now wondering if this user will ever complain about "we're no longer accepting questions from this account"...) – Arjan Apr 10 '11 at 21:04

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))```

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).