Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Formula to ignore hidden/filtered rows for text

0

Hello,

I'm trying to figure out how to write a formual to look up a specific text item in a column while ignoring anyting that has been filtered out

=IF(TRIM(B2)<>"",(IFERROR(VLOOKUP(TRIM(B2),'Locations Needed'!A:A,1,FALSE),"Not Needed")),"")

above is the current formual im working with that does what i need it to do by manually entering in data to the 'Locations Needed' sheet. I've added a master list that when locations are no longer needed, they will be filtered out/hidden but not deleted. The current version does not ignore the filtered out text and i'd like for it to do so. Any help would be greatly appreciated.

excel
posted by: ExcelWell (rep: 2) on Tue Mar 29, 2022 at 6:57 pm
Answer
Discuss

Answers

0
Selected Answer

Hi ExcelWell and welcome to the Forum.

You don't say (in your Profile) which version of Excel you use so I've suggested a solution which should work with older versions of Excel.

Excel's Subtotal function can be used to ignore hidden data (see Don's tutorial Exclude Cells that are Filtered, Hidden or Grouped from Formulas) and here I suggest using it combined with Index/Match functions (a better version of VLOOKUP). Here I've used the 103 argument within Subtotal which will ignore both filtered and manually hidden data).

The formula below should show "Not needed" if B2 isn't found but "Not needed (filtered or hidden)" if B2 is found but it's not visible:

=IFERROR(IF(SUBTOTAL(103,INDEX('Locations Needed'!A:A,MATCH(TRIM(B2),'Locations Needed'!A:A,0)))=0, "Not needed (filtered or hidden)", ""),"Not needed")

Just delete the "(filtered or hidden)" bit if you don't want that detail.

Hope this helps.

posted by: John_Ru (rep: 6142) on Wed Mar 30, 2022 at 8:20 am
Discuss

Discussion

Hey John,Ru,
Thank you for the warm welcome to the forum, this works perfect! Thank you so much! I knew there was a way to get this but I totally hit a wall and was trying to watch every tutorial video i could get my hands on! I'll be spending lots of time here reading through all of the questions and watching every tutorial i can find so I can evetually say that I excel well at Excel! (I'll go ahead and update my profile with what excel version im using in case i have futher questions)
ExcelWell (rep: 2) Mar 31, '22 at 7:06 pm
I'm happy that worked for you, ExcelWell. Thanks for selecting my answer.

Good luck with your learning plan. You'll learn a lot from this site and find that a little knowledge goes a long way (on my case). You should perhaps consider Don's paid courses too. 
John_Ru (rep: 6142) Apr 1, '22 at 2:26 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login