VLOOKUP() function to work only on visible cells of filtered range


The VLOOKUP() function returns data from a lookup_array irrespective of the filter setting of the lookup_array.  To make the lookup_array of the VLOOKUP() function work only on the visible cells of a filtered range, refer to this workbook.

Leave a Comment

Your email address will not be published.


  • Thanks in Advance,

    Would also like to know

    1.If I drag some fields at Pivot, In my source document How do I identify which fields I have selected – any trick to Highlight – Identify them
    2. How do I identify at Pivot, in my source data which filed Header names have been changed?, seems my pivot stops refreshing : A message saying your source data field name has been changed
    Please email me

  • Your formula is great but only works with unique values, if you repeat the same letter (i.e. “E”) and hide the first value of “E”, you will get the hidden value, so it doesn’t work in this case.
    would a definite solution to this?.

      • ok now it hides from seen it but there is other “E” that is not hidden and does not show the correct result in the lookup, remember that is the first “E” that was hidden the others are still there.
        I hope we find the answer

      • ok now it does not see the hidden value but it does not see also the rest of the visible ones, remember that we hide only one of the “E” values (the first) but there must be others not hidden
        I hope we could find a formula solution
        Thanks in advance