Select a cell based on an other cell's value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wally_91 - 11 Mar 2008 19:58 GMT

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

Signature

wally_91

Reply to this Message

Pete_UK - 11 Mar 2008 21:20 GMT

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

On Mar 11, 6:58 pm, wally_91 <wally_91.21c9...@officefrustration.com>
wrote:

> Hi!
>
[quoted text clipped - 22 lines]
> --
> wally_91

Reply to this Message

wally_91 - 12 Mar 2008 00:28 GMT

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

Pete_UK;2668273 Wrote:

> Put this in C6:
>
[quoted text clipped - 40 lines]
> --
> wally_91-

Signature

wally_91

Reply to this Message

wally_91 - 12 Mar 2008 22:19 GMT

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;2668273 Wrote:

> Put this in C6:
>
[quoted text clipped - 40 lines]
> --
> wally_91-

Signature

wally_91

Reply to this Message

Pete_UK - 13 Mar 2008 01:24 GMT

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

Pete

On Mar 12, 9:19 pm, wally_91 <wally_91.21e0...@officefrustration.com>
wrote:

> Hi again!
>
[quoted text clipped - 63 lines]
>
> - Show quoted text -

Reply to this Message