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

Hidden rows values reflected while using vlookup

0

Dear Experts,

I have two sheets in which sheet 1, emp codes are given but names are blank, sheet 2, emp codes and names are given but emp code 2 and 5 I have hidden the row.

Now, when I enter vlookup formula in sheet 1 to attain the names, it also gives values for emp code 2 and 5(which were hidden in sheet 2).

How to avoid that in Sheet 1? Whats the formula to be used so that hidden rows(emp codes 2 and 5 in Sheet 2) do not reflect using vlookup in sheet 1? (hence, all the names should appear in Sheet 1 but excluding emp codes 2 and 5).

Thanks and Regards,

Akash Sharma

excel
posted by: Akash Sharma (rep: 40) on Mon May 14, 2018 at 3:07 pm
Answer
Discuss

Answers

0
Selected Answer

Please try this formula.

=IFERROR(IF(SUBTOTAL(103,INDIRECT(ADDRESS(MATCH($A2,Sheet2!$A$1:$A5,0),1,1,1,"Sheet2"))),VLOOKUP($A2,Sheet2!$A$2:$B$8,2,FALSE),""),"")

For better readability and easier management I recommend that you create two named ranges on Sheet2 as follows. Extend the end beyond the immediate need but avoid including all 1.4 million rows.

  1. CodeColumn ($A$1:$A$5)
    This range is used by the MATCH function. It must start in row 1.
  2. CodeList ($A$2:$B$5)
    This is the range VLOOKUP uses.

With the named ranges set the above formula would look like this.

=IFERROR(IF(SUBTOTAL(103,INDIRECT(ADDRESS(MATCH($A2,CodeColumn,0),1,1,1,"Sheet2"))),VLOOKUP($A2,CodeList,2,FALSE),""),"")

At the heart of this formula is the SUBTOTAL(103 function which returns 0 if the counted cell is invisible, otherwise 1.

Finally, here is an improved version of the above, created on second thought.

=IFERROR(IF(SUBTOTAL(103,INDEX(CodeColumn,MATCH($A2,CodeColumn,0))),VLOOKUP($A2,CodeList,2,FALSE),""),"")

This function does the same thing but avoids the rather cumbersome INDIRECT(ADDRESS replacing it with the Reference type of the INDEX function. In fact this type is intended for precisely this use and there is no need to circumvent it. I'm leaving the original here for its instructive value. You should prefer to deploythe improved final version  in your own workbook.

posted by: Variatus (rep: 4889) on Mon May 14, 2018 at 9:37 pm
Discuss

Discussion

Hi, please find attached file, not working, please help out.

Also, do we have apply such a long formula?

Thanks and Regards,
Akash Sharma
Akash Sharma (rep: 40) May 15, '18 at 9:47 am
Your formula in C2 works perfectly except for one small mistake. The referenced tab isn't "Sheet 2". It's "Sheet2", without space. Wouldn't happen with a named range. (Select the range and type its name in the Name Bar - top left next to the Formula Bar. Modify using the Name Manager on the Formulas tab.) There also is a rather big mistake in that you use relative addressing (automatically corrected by Excel if you use a named range instead lol:). As you copy Sheet2!A1:B5 down the address changes to Sheet2!A2:B6 etc. Absolute addressing will prevent that. Here is the improved formula.
[Sheet1!C2] =IFERROR(IF(SUBTOTAL(103,INDIRECT(ADDRESS(MATCH($A2,Sheet2!$A$1:$A$5,0),1,1,1,"Sheet2"))),VLOOKUP($A2,Sheet2!$A$1:$B$5,2,FALSE),""),"")
I suggest you improve it further by replacing INDIRECT(ADDRESS with INDEX as explained in my answer above.
Variatus (rep: 4889) May 15, '18 at 10:09 pm
Hello Akash,
I'm glad youj got it to work. Please remember to mark my answer as "accepted". Thank you. Have a great day!
Variatus (rep: 4889) May 20, '18 at 8:02 pm
Hi Variatus,
How to accept your final answer, option not available as we are expanding our discussions.
Akash Sharma (rep: 40) May 25, '18 at 11:39 am
Hello Akash,
At the bottom of the answer, just above my name and the date of the post, you should have a blue button with the caption "Select answer". That's the button to click.
You may have followed my correspondence with Don about the missing "Select All" button. I asked this question because I noticed you were copying my formulas manually, introducing errors, instead of copying them to the clipboard. I found that I couldn't copy them either. In response Don found an error in his coding and repaired it. The button was available for Google Chrome browsers but not for IE. Please check if you have the button now.
Perhaps its the same for the "Select Answer" button. I have it on my screen. If you don't, let us raise the problem with Don. He probably doesn't know. The difference would be the Browser (name and version).
Variatus (rep: 4889) May 25, '18 at 9:53 pm
It seems that you overlooked accepting this answer. If so, please accept it now. Thank you.
Variatus (rep: 4889) May 26, '18 at 9:32 pm
Add to Discussion
0

Hi Variatus. please find enclosed file, need your suggestion.

PLEASE find updated latest file as on 17th May 2018.

posted by: Akash Sharma (rep: 40) on Tue May 15, 2018 at 9:50 am
Discuss

Discussion

Hi Variatus,
Thanks for replying back, pls find attached revised sheet, I am confused.

Thanks and Regards,
Akash Sharma
Akash Sharma (rep: 40) May 16, '18 at 2:58 pm
Hi Akash,
Your problem results from the missing $ signs (absolute addressing) in your formula. Without the $ signs (relative addressing) the formula will only work in the first row. To avoid this problem completely I suggest that you set up the two named ranges I have suggested, then paste the exact third and final formula from my original answer into your test sheet and copy down.
To define a named range select the range and type its name in the Name Bar (the field to the left of the Formula Bar above the spreadsheet grid which normally shows the selected cell's address). You can modify the initially selected range later by using the Name Manager dialog box which you find on the Ribbon's Formulas tab.
For your convenience I have added a workbook demonstrating two solutions on Sheet1. Column C has formulas using INDIRECT(ADDRESS and direct range addressing. Column D uses the INDEX function and named ranges. It is much shorter, as you see.
Variatus (rep: 4889) May 18, '18 at 8:03 am
Thanks a lot.
Akash Sharma (rep: 40) May 20, '18 at 1:13 pm
Add to Discussion


Answer the Question

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