Vlookup

[Khalil Handal]

Range1 is the name of the range d3:AA33 in the sheet "chart". This range
represent 12 months of the year, for each month TWO colomn the first is for
the date and the second has a value (a value for each day of the year).
C7 in sheet3 has a date value.

in (sheet3) I need to have a formula as follows:
take the value for the corresponding date (month and day) in sheet "chart"
that has the same date as in C7 and multiply it with value in cell H7
(sheet3) and put the result in cell I7.

i.e. if c7 = 26/06/07 , vlookup for that date from sheet "chart" take the
value next to it (on the its right in the same line) and multiply it with
the value in cell H7 sheet3 and put the result in cel I7.

Hope it is clear.

 

[Ragdyer]

Try this in I7:

=H7*SUMPRODUCT((chart!D3:Z33=C7)*chart!E3:AA33)

 

[Khalil Handal]

=H7*SUMPRODUCT((chart!D3:Z33=C7)*chart!E3:AA33) in the cell gave the
following error: #value!

a value used in the formula is of the wrong data type.
Is it possible to use the range name of "range1" for the cells subject
D3:AA33 that containes the values

 

[Ragdyer]

Can't use the range name (range 1) with this formula because, as you see,
the first and second arguments *don't* include the *entire* range.

That error means that you might:
1] have alpha text in one of the number columns,
2] have a zero length string (null - "" ) in one of the number columns,
3] have a <space> in one of the number columns.

If you are populating your number columns with formulas, where they may
return a null ( "" ), then you can try the unary version of Sumproduct.

Let me know if this works:

=H7*SUMPRODUCT(--(chart!D3:Z33=C7),chart!E3:AA33)

This form *will* allow non-numeric entries in the number columns.

 

[Khalil Handal]

Yes, it worked fine.
Thanks

Can't use the range name (range 1) with this formula because, as you see,
the first and second arguments *don't* include the *entire* range.

That error means that you might:
1] have alpha text in one of the number columns,
2] have a zero length string (null - "" ) in one of the number columns,
3] have a <space> in one of the number columns.

If you are populating your number columns with formulas, where they may
return a null ( "" ), then you can try the unary version of Sumproduct.

Let me know if this works:

=H7*SUMPRODUCT(--(chart!D3:Z33=C7),chart!E3:AA33)

This form *will* allow non-numeric entries in the number columns.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

=H7*SUMPRODUCT((chart!D3:Z33=C7)*chart!E3:AA33) in the cell gave the
following error: #value!

a value used in the formula is of the wrong data type.
Is it possible to use the range name of "range1" for the cells subject
D3:AA33 that containes the values




- !
-

 

[RagDyeR]

You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Yes, it worked fine.
Thanks

Can't use the range name (range 1) with this formula because, as you see,
the first and second arguments *don't* include the *entire* range.

That error means that you might:
1] have alpha text in one of the number columns,
2] have a zero length string (null - "" ) in one of the number columns,
3] have a <space> in one of the number columns.

If you are populating your number columns with formulas, where they may
return a null ( "" ), then you can try the unary version of Sumproduct.

Let me know if this works:

=H7*SUMPRODUCT(--(chart!D3:Z33=C7),chart!E3:AA33)

This form *will* allow non-numeric entries in the number columns.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

=H7*SUMPRODUCT((chart!D3:Z33=C7)*chart!E3:AA33) in the cell gave the
following error: #value!

a value used in the formula is of the wrong data type.
Is it possible to use the range name of "range1" for the cells subject
D3:AA33 that containes the values




- !
-