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

Answer
Discuss

Answers

0

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.

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: 2) 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: 1218) 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: 1218) May 20, '18 at 8:02 pm
Add to Discussion
0

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

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

Discuss

Discussion

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

Thanks and Regards,
Akash Sharma
Akash Sharma (rep: 2) 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: 1218) May 18, '18 at 8:03 am
Thanks a lot.
Akash Sharma (rep: 2) 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