Lookup-value in HLOOKUP?

[Chrisp]

It seems you can't use a formula (eg ">0") as the lookup value in VLOOKUP or
HLOOKUP. Is there a way around this? I have month by month sales data for
a few thousand items for 36 months and I am trying to establish how many
months ago was the last sales activity.

 

[Chrisp]

Hi,

I have table of 36 months of historical data
-Column headings are month/year in order (oldest to newest left to right)
-Row headings are product codes
-Data is the quantity sold for each product each month

With a few thousand products, my challenge is to find a quick way of
calculating for each product how many months have elapsed since the last sale
- ie how many zeros, if any, at the right hand end of each row. This is to
identify products which may be obsolete.

I thought that using HLOOKUP might have some potential, but I am beginning
to think that this may have to be a macro solution.

Would certainly appreciate any ideas you may have.

Many thanks

Chris P

 

[Chrisp]

Hi smartin,

My apologies for the delay in replying - had hardware problems.

Your proposal is as effective as it is neat. I had put together a very
clunky solution that involved creating another 36 columns that could be
hidden later, each containing a formula to count blank columns. Your idea is
infinitely better.

Many thanks again,

ChrisP