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.

 

[smartin]

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.

Hi Chrisp, Show us how you have your data organized.

 

[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

 

[smartin]

Hi Chrisp,

With 36 months in columns B:AK starting in row 2, the following array
formula* placed in a convenient column to the right of your data will
return the right-most column number with a non-zero value (*array
formula -- commit with Ctrl+Shift+Enter):

=MAX(--($B2:$AK2<>0)*COLUMN($B2:$AK2))

a quick modification tells you how many right-trailing zeros you have on
each row (again, an array formula):

=37-MAX(--($B2:$AK2<>0)*COLUMN($B2:$AK2))

Hope this helps!

 

[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