Vlookup to insert num in mult. columns

[Duplatt]

I have a 40 year Capital Item Replacement Program.
This formula is in Row 4 , Columns B thru A
=if(ISNA(VLOOKUP(H2,Sheet7!$C$19:$C$D28,2,False)),0,VLOOKUP(H2,Sheet7!$C$19:$D$28,2,False))
Row 2, Column B thru AP =2002 to 2042. Therefore B2=2002 & H2 = 2008 etc.
The number in the reference cells on Page 7 is variable depending on a
number input into another cell on Page 7.
For instance an article installed in 1995 to be replaced in 13 years would
be replaced in 2008. So far it works and puts a number in cell H4.
My question, how do I get it to put a number in the 26th & 39th year?
Any help appreciated.
Thank You -- Duane Platt

 

[Max]

Did come across your earlier posting in .newusers, but couldn't grasp your
set-up/issue.

Think your setup description is incomplete. Explain and illustrate your
set-up in Sheet7, eg paste what you have in the vlookup's table array
Sheet7!$C$19:$D28.

I can presume that C19:C28 in Sheet7 possibly contains the lookup years, eg:
2002, 2003 . but I don't know what value that the vlookup is returning, ie
what's in D1928, and how the vlookup/its result relates to your question:

.. how do I get it to put a number in the 26th & 39th year?

Also, is there a possible inconsistency somewhere in that the vlookup table
array houses only 10 lookup years C19:C28 but you have 41 lookup years
placed in B2:AP2 in your formula sheet, viz: 2002 - 2042 (your vlookup is
looking for exact match, hence there should be at least 41 lookup years in
Sheet7's table array, no?)

Could you also explain / illustrate further on this part:

The number in the reference cells on Page 7 is variable depending on a
number input into another cell on Page 7.
For instance an article installed in 1995 to be replaced in 13 years would
be replaced in 2008.

---

 

[Duplatt]

Max. Thank you for responding
This is the range on page 7. There are several ranges like this.
The program is for tracking when to replace Capital items for our condo
association. This particular range is for 'Porch Rails' the 13 is a variable.
if changed to 14 then column D would change correspondingly.

Sheet 3 lists all the items to be replaced in the A column
Sheet 3 row 2 lists all the years fromm 2002 to 2042
Each cell below row 2 has the Vlookup formula.
It works fine but, only for the years listed on sheet 7. C1928.
I think that by adding more columns for the lookup range it would work.
I don't know how to add additional Vlookup's to the formula
Any help appreciated
Merry Christmas - Duane
C D Porch Rails 13
Built Replace Quantity
19 1995 2008 4
1996 2009 7
1997 2010 4
1998 2011 1
1999 2012 8
2000 2013 3
2001 2014 2
2002 2015 2
2003 2016 0
28 2004 2017 0

 

[Max]

Afraid it's still tough to figure out what's actually happening over there

Perhaps you could upload a working sample of your actual* file
via a free filehost & post the link to it in response here?

You could use either of these free filehosts:
http://www.freefilehosting.net/
http://cjoint.com/index.php

*desensitize it as required

 

[Duplatt]

Thank you Max
It's a little too late for me to attempt that tonight. Will construct
something tomorrow and post
Duane

 

[Duplatt]

Hi Max
I posted to www.freefilehosting.net/dowmload/39ck4
Sheet 7 and 3A are my concern. Sheet 3A is automatically updated from Sheet
7. Sheet 3 is manually updated. (an option)
No line item is expected to be replaced in less than 13 years.
I probably need to add 2 more columns for each catagory range on sheet 7 for
additional years.
Example, for a unit built in 1995 the porch rails would br expected to be
replaced in 2008 - 2021 & 2034.
I don't know how to alter the Vlookup formulas on Sheet 3A to reflect the
added columns on Sheet 7.
Any advise appreciated -- Duane

 

[Max]

Think your earlier link should have read:
http://www.freefilehosting.net/download/39ck4
(you had a typo there in "download")

Here's a suggested set-up using index/match
which should do it here ..

Illustrated in this sample:
http://cjoint.com/?mxixGcos1o
Cyclic Replacement via Index n Match.xls

In Sheet7,

For Porch Rails,
Insert 2 new columns D & E to cater for
the next 2 cycles of replacements, eg: Replace2 & Replace3
Place in D19: =C19+$F$17
Copy D19 across to E19, fill down to E28

Then in Sheet3A,
Put in B4:
=IF(ISNA(MATCH(B2,Sheet7!$C$19:$C$28,0)),
IF(ISNA(MATCH(B2,Sheet7!$D$19:$D$28,0)),
IF(ISNA(MATCH(B2,Sheet7!$E$19:$E$28,0)),"",
INDEX(Sheet7!$F$19:$F$28,MATCH(B2,Sheet7!$E$19:$E$28,0))),
INDEX(Sheet7!$F$19:$F$28,MATCH(B2,Sheet7!$D$19:$D$28,0))),
INDEX(Sheet7!$F$19:$F$28,MATCH(B2,Sheet7!$C$19:$C$28,0)))
Copy B4 across to AP4. This would return the results that you seek.

Just repeat the same construct accordingly for the other lines. In the
sample, I've done it for the Gutters - Single, Double & Quad lines as a
further example.

 

[Duplatt]

Max
That is super
Thank you & Merry Christmas
Duane

 

[Max]

Welcome, Duane.
Merry Christmas