Select a cell based on an other cell's value

[wally_91]

Hi!

I've got an excel-table. It looks something like this.

Table1
(A1)Month (B1)SMS
January 106
February 220
Mars 50

(A6)Max: =MAX(B2;B4)
(A7)Min: =MIN(B2;B4)

Now I want to find out what month B6 and B7 is and placing them in C6 &
C7.

In SQL I would have done something like

SELECT `month` FROM table1 WHERE SMS = B6

How do I do this?!

Many thanks, wally_91

 

[Pete_UK]

Put this in C6:

=INDEX(A2:A4,MATCH(B6,B2:B4,0))

and this in C7:

=INDEX(A2:A4,MATCH(B7,B2:B4,0))

If you have two maximum values or two minimum values, the MATCH
function will find the first.

Hope this helps.

Pete

 

[wally_91]

Thanks for the response!

I can't get this to work... I only get an errormessage:

http://img528.imageshack.us/img528/2012/200803120019dz6.png
(this is from Numbers but I get an error in excel too. It doesn't say
what though)

Thank you

Wally_91

 

[wally_91]

Hi again!

I got my answer at another place.

In C7 put

=offset(a1;match(b6;b2:b4;0);0)

In C8 put

=offset(a1;match(b7;b2:b4;0);0)

Thanks anyway (love this forum!!!)

wally_91

 

[Pete_UK]

Glad you found an answer to your problem, Wally - thanks for feeding
back.

Pete