Sumproduct & Filters

[icsonu]

I want to find out a Specific Word in a Column after I have applied a filter
to specific Column.

Column A Column B
Apple 95$
Apricot 120$
Banana 90$
Grapes 80$
*
*
*
And So On.

I want to display "Apple" in Cell C45 after I apply the filter to Column B
for say Fruits less than 100$, as the Column A display Grapes,Apple & Banana
too.
Can you pls help me out just to show the Apple in a Cell say C45, even
though banana & grapes are displaying in the Column A.

Regards
Sonu

 

[T. Valko]

Try this array formula** :

The entire unfiltered range is A2:A15.

=INDEX(A2:A15,MATCH(1,SUBTOTAL(3,OFFSET(A2:A15,,,ROW(A2:A15)-MIN(ROW(A2:A15))+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[icsonu]

Thanks T. Valko the formula helped me a lot, but still I have a problem your
reference array MATCH(1, in the formula showed me the first word in the
column of the displayed filter result , Is there any way of finding the Exact
Text say "Apple" or "Grapes" which is also less than 100$.

 

[T. Valko]

the formula showed me the first word in the

column of the displayed filter result

I thought that's what you wanted.

I don't understand what you want???