Formula to extract text from range.

[Jackson Brown]

I have got a range with text and number in their respective columns.
My problem is a simple one how do I get a formula to list text value.
Header cells are C1 to F1
Data are in cells C2 to F100
Col C is text Col D is numerical but no required . Col E is text
and Col F is numerical.

Here is my problem
If C2:C100 = G2 and max(F2:F100) output E2:E100( which is a text
value)
I have tried ALL of the wrong formulae.
Help.

 

[Biff]

Hi!

Try something like this:

Entered as an array: CTRL,SHIFT,ENTER

=INDEX(E2:E100,MATCH(1,(C2:C100=G2)*(F2:F100=MAX
(F2:F100)),0))

Biff

 

[Jackson Brown]

Hi Biff,
I tried this formula and it worked for the first search, i.e
($C$2:C$100=G2), But when I try ($C$2:C$100=G3) leaving the rest of
the formula intact I get #N/A either by dragging it down or entering
the formula manually.
There are twelve different names in Col C so in Col G I have listed
them from G2 to G13.
Can this still be done?

 

[Frank Kabel]

Hi
have you entered this formula as array formula with CTRL+SHIFT+ENTER?

 

[Jackson Brown]

Hi
have you entered this formula as array formula with CTRL+SHIFT+ENTER?

Yes it has the { } brackets.