Tricky Formel (counting days in combination with lookup)

[chrismania]

Hey Guys,
I try to figure out a formel which I need for counting the days between
days.
I have two tables with the following structure
2003 2004
Comp Est. Start Date Company Est. Start Date

BT 23/03/2003 Dell 22/04/2004
Cable 24/05/2003 BT 22/05/2004
Virgin 22/06/2003 Virgin 28/06/2004

What I want do do now is to count the days between the Estimated Start
Dates if a company is listed in 2003 and 2004 (BT and Virgin).
If the company is not listed in 2003 and 2004 the cell should be empty
(Cable)
I think the formule must contain vlookup sum and data array function.
The attachment contains the table.

Thank you in advance

Christoph

File Attached: http://www.excelforum.com/attachment.php?postid=320674 (book1.xls)

 

[Philk]

Christoph,

This should work if you name your 2003 range as YR03 and
your 2004 range as YR04.

=IF(ISERROR(VLOOKUP(A16,YR04,2,FALSE)-VLOOKUP
(A16,YR03,2,FALSE)),0,VLOOKUP(A16,YR04,2,FALSE)-VLOOKUP
(A16,YR03,2,FALSE))

 

[Philk]

Christoph,

Sorry should reread before I post. A16 refers to a cell
that contains the Comp (Dell, Virgin, BT).

Hope this helps.

-----Original Message-----
Christoph,

This should work if you name your 2003 range as YR03 and
your 2004 range as YR04.

=IF(ISERROR(VLOOKUP(A16,YR04,2,FALSE)-VLOOKUP
(A16,YR03,2,FALSE)),0,VLOOKUP(A16,YR04,2,FALSE)-VLOOKUP
(A16,YR03,2,FALSE))

-----Original Message-----
Hey Guys,
I try to figure out a formel which I need for counting the days between
days.
I have two tables with the following structure
2003 2004
Comp Est. Start Date Company Est. Start Date

BT 23/03/2003 Dell 22/04/2004
Cable 24/05/2003 BT 22/05/2004
Virgin 22/06/2003 Virgin 28/06/2004

What I want do do now is to count the days between the Estimated Start
Dates if a company is listed in 2003 and 2004 (BT and Virgin).
If the company is not listed in 2003 and 2004 the cell should be empty
(Cable)
I think the formule must contain vlookup sum and data array function.
The attachment contains the table.

Thank you in advance

Christoph

File Attached: http://www.excelforum.com/attachment.php? postid=320674 (book1.xls)

------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.

.