Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
1  
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

2 Answers 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:

enter image description here

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

share|improve this answer

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))
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.