Lookup only visible cells

  • Normal lookups include data even if it is hidden or part of the excluded portion of a filtered list.


    Is there a function / combination of functions that will return values only for the visible portion? If not, I can always write a UDF, but I'd like to avoid that if possible.

  • Re: Lookup only visible cells


    Assuming that A1:C10 contains your data, the first row contains your headers/labels, A15 contains your lookup value, and you want to return the corresponding value in Column C, try...


    =VLOOKUP(A15,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!