Select a cell based on an other cell's value

[webbywally]

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

 

[SkipVought]

Hi,

Min & Max in rows 6 & 7?

What happens when you add April et al? the LOCATION and FORMULA has to change! NOT a good design, IMHO.

I recommend putting aggregations ABOVE the table, unless you intend to use the Table as a Query source, in which case NO AGGREGATIONS should appeat in that sheet.

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

Well what happens when TWO or MORE months have the same Min or Max?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[webbywally]

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)

 

[SkipVought]

Yes and no...

"Well what happens when TWO or MORE months have the same Min or Max?"


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[webbywally]

Well, the 0 in MATCH makes it select the first occurrence of what MATCH finds. I'm fine with just one month btw.