Vlookup Against Value In First Visible Cell

I have a filtered list in excel and say the first row of filtered data was A400. how can I do a lookup against this first cell. Obviously every time I filter against a different part nmber the first row changes.

macke

Assumptions:

A2:C10 contains the data

E1 contains the lookup value

The corresponding value in Column C is to be returned

Formula:

=VLOOKUP(E1,IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1)),A2:C10)
,3,0)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <macke.26gpgc_1145367306.2282@excelforum-nospam.com>,
macke <macke.26gpgc_1145367306.2282@excelforum-nospam.com> wrote:

> I have a filtered list in excel and say the first row of filtered data
> was A400. how can I do a lookup against this first cell. Obviously
> every time I filter against a different part nmber the first row
> changes.
>
> macke

Hi

The formula works fine, but I guess my requirement wasn't quite clear.

I need the to use the value in the first cell as my lookup value to be able to extract the data from another spreadsheet.

Sorry

macke

To return the first visible value in a filtered list, try...

=INDEX(A2:A100,MATCH(1,SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1
)),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Then, use this
as the lookup value for the lookup formula.

Hope this helps!

In article <macke.26ipg0_1145460601.9995@excelforum-nospam.com>,
macke <macke.26ipg0_1145460601.9995@excelforum-nospam.com> wrote:

> Hi
>
> The formula works fine, but I guess my requirement wasn't quite clear.
>
> I need the to use the value in the first cell as my lookup value to be
> able to extract the data from another spreadsheet.
>
> Sorry
>
> macke

Hi

Yes that works brilliantly. Thank you

macke

Hi,

I am also i need of this formula.
However, I am not familiar with complex formulas such as these. The formula worked for returning the value of A2 in the filtered list. I also need the value for A3, A4 etc. so what part of the formula do i change for this?
Thanks