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

[mgris]

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.

 

[xld]

Show us what you have, and maybe a data sample.

 

[mgris]

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.

 

[Luke 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.

 

[mgris]

Thx Luke,

I'll check your solution as soon as I can, and will let you know.

Cheers,

M.