vlookup to ignore hidden rows

[mr_hiboy]

Hi,


I have a large dataset with products and promotions info which I have filter to only show certain products (which I want in the promo).


In another sheet I have a list of all products and I simply want to state whether or not the product is filtered on the other sheet, a summary if you like of which products are in the promo.


I am currently using =IF(VLOOKUP(C4,PromoTool!$B$6:$E$18362,1,FALSE)=C4,"Yes","No")


Where C4 is a product, so if it finds it in the large list then say yes, otherwise say no. I'm getting a "Yes" as the product is in the list, but hidden as filtered out.


Thanks

Paul

 

[Luke M]

On the PromoTool sheet, we need to add a helper column (let's say column Z) to help us detect filtered rows. In this column, we'll have this formula:

=SUBTOTAL(103,B6)

copied down. This formula will give a value of 1 if the row is visible, otherwise it will be 0. On your product sheet then, formula becomes:

=IF(INDEX(PromoTool!$Z$6$18362:$Z,MATCH(C4,PromoTool!$B$6:$B$18362,0)),"Yes","No")

 

[mr_hiboy]

Cheers Luke, you had me thinking with that little typo in the INDEX $Z$6$18362:$Z range but got there.


Thanks again

 

[Luke M]

Oops, sorry about that. Guess I'm not completely awake yet. =P

Should have been:

=IF(INDEX(PromoTool!$Z$6:$Z$18362,MATCH(C4,PromoTool!$B$6:$B$18362,0)),"Yes","No")

 

[Haseeb A]

Hello Paul,


Since INDEX can produce range references, you can avoid helper column.


=IF(SUBTOTAL(103,INDEX(PromoTool!$B$6:$B$18362,MATCH(C4,PromoTool!$B$6:$B$18362,0))),"Yes","No")


If C4 doesn't found in PromoTool!$B$6:$B$18362 will give an error.


Haseeb

 

[Luke M]

Even better!