Vlookup Against Value In First Visible Cell

[macke]

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

mack

 

[Domenic]

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!

 

[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 b
able to extract the data from another spreadsheet.

Sorry

mack

 

[Domenic]

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!

 

[macke]

Hi

Yes that works brilliantly. Thank you

macke