Hello all,
I have a problem with excel which is best described using this picture:
http://img27.imageshack.us/img27/9782/excelt.jpg

What I want is for the red square to give me the factory which produced the most widgets, in this case Factory 2. The square above uses the max formula.

Is this possible? Is there an alternative layout if not?

Although I am doing this in 2k7 i am hoping there is a simple way to achieve this so that it is compatible with 2k and 2k3 without any VBA.

What do you think?

Thanks

You could use simple nested if

=IF(C16=C4,A4,IF(C16=C8,A8,A12))

Brian

You could use simple nested if

=IF(C16=C4,A4,IF(C16=C8,A8,A12))

Brian

Ah, it seems I've overlooked the obvious :D...The problem is that the list of factories could get long so the nesting could also get long and complicated :eek:

You could do:

=INDEX(A4:A14,MATCH(C16,C4:C14,0),1)

Ah, it seems I've overlooked the obvious :D...The problem is that the list of factories could get long so the nesting could also get long and complicated :eek:

What are the plans if 2 or more are equal max ?

Brian