• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup in Subtotal = Problems...... Address in Subtotal = Problems :(

mgris

New Member
Hi,


I was trying to do a subtotal that contained a vlookup formula.

It didn't work...

I realized that subtotal needs a cell reference to function, and not a value...

I've tried few things with the Address function, but if I'm right it returns the address as a string content and does not "refer" to the cell itself...


Do you have any idea on how to correct my mistakes?


Thx,


M.
 
Hi xld,


Hmmm... A data sample would require lengthy explanations.


So just to try to make it clearer, here is what I'm trying to do in one single formula:


- Search a certain value with vlookup

- Once the value is found, get the address of the cell that contains it

- In order to use it in a subtotal...


Hope it will help you help me :)


Cheers,


M.
 
VLOOKUP is not needed (it's somewhat overkill) since the real question is what row is the data in, not what the value is. Perhaps something like:

=SUBTOTAL(Function#,B1:INDIRECT("B"&MATCH(LookupValue,A:A,0)))


Since you didn't post your formula, I had to make several guesses.

I'm assuming your looking at a range of stuff to subtotal, not just 1 cell (else, why "total" it?)

I assumed the your vlookup was originally looking for some value in col A and returning the value from col B. You will need to change those reference if this is not correct.
 
Back
Top