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.
VERY helpful, VERY simply and well explained!
-
Thank you. I am glad you liked this post.
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
-
Hi,
1. Question not clear
2. After creating a Pivot Table, if you change the column heading, then the Pivot will not update. You will have to recreate the Pivot Table.
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?.
Thanks.
Audie
-
Hi,
In my formula replace the first input of the SUBTOTAL function with 103 (instead of 3). Now press Ctrl+Shift+Enter.
Hope this helps.
-
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
thanks
Audie -
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
Audie-
-
Sorry, the link ask me for an email but bencosmea@gmail.com doesn’t work
-
Hi,
There should be no requirement to enter any credentials. Please retry.
-
should you repost the link, same happens with the last one.
still saying…
Sing in…tell us the account you want to use to open
“exelenthusiasts.sharepoint.com/Documents/”
-
-
-
VLOOKUP() function to work only on visible cells of filtered range
{ 12 Comments }