Hello,

How do you get excel to ignore certain data when reading a cell?

I want to use VLOOKUP to match a cell reading "NP001" with "N001"

Or even better I want to use it in a SumProduct Formula:

=SUMPRODUCT((A1:A10="N001")*(B1:B10=--"02/06/06");C1:C10)

Thanks,
P

Nel post
*Ross* ha scritto:

Hello,

How do you get excel to ignore certain data when reading a cell?

I want to use VLOOKUP to match a cell reading "NP001" with "N001"

Or even better I want to use it in a SumProduct Formula:

=SUMPRODUCT((A1:A10="N001")*(B1:B10=--"02/06/06");C1:C10)

Thanks,
P

Hi Ross,

maybe this is good for you:

=SUMPRODUCT((SUBSTITUTE(A1:A10,"P","")="N001")*(B1 :B10=--"02/06/06");C1:C10)


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Thank you that does work to an extent, however my cell keeps returning #VALUE
as it is text I am trying to return not numbers, any suggestions on a way
round this?

Thanks,
Ross



"Franz Verga" wrote:

Nel post
*Ross* ha scritto:

Hello,

How do you get excel to ignore certain data when reading a cell?

I want to use VLOOKUP to match a cell reading "NP001" with "N001"

Or even better I want to use it in a SumProduct Formula:

=SUMPRODUCT((A1:A10="N001")*(B1:B10=--"02/06/06");C1:C10)

Thanks,
P

Hi Ross,

maybe this is good for you:

=SUMPRODUCT((SUBSTITUTE(A1:A10,"P","")="N001")*(B1 :B10=--"02/06/06");C1:C10)


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Nel post
*Ross* ha scritto:

"Franz Verga" wrote:

Nel post
*Ross* ha scritto:

Hello,

How do you get excel to ignore certain data when reading a cell?

I want to use VLOOKUP to match a cell reading "NP001" with "N001"

Or even better I want to use it in a SumProduct Formula:

=SUMPRODUCT((A1:A10="N001")*(B1:B10=--"02/06/06");C1:C10)

Thanks,
P

Hi Ross,

maybe this is good for you:

=SUMPRODUCT((SUBSTITUTE(A1:A10,"P","")="N001")*(B1 :B10=--"02/06/06");C1:C10)

Thank you that does work to an extent, however my cell keeps
returning #VALUE as it is text I am trying to return not numbers, any
suggestions on a way round this?



But in your first post you said you want to use SUMPRODUCT.. This function
is used to make calculation, so need almost 1 range with numbers... If you
have only text, we have to change... Try to post an example of the way you
would use VLOOKUP...

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Im not really sure what VLOOKUP to use or where to start I want to return the
HDR data from belwo table using a reference cell stating "S59" and a cell
stating the Date "1/4/2006 00:00"

Code ProdDate OnLine Hdr
SP59 01/04/2006 00:00 24 HDR2
SP69 01/04/2006 00:00 24 HDR1
MS19 01/04/2006 00:00 0 TEST
SP28 01/04/2006 00:00 0 SHUT
SP33 01/04/2006 00:00 24 HDR1
SP50 01/04/2006 00:00 24 HDR2

Thanks,





"Franz Verga" wrote:

Nel post
*Ross* ha scritto:

"Franz Verga" wrote:

Nel post
*Ross* ha scritto:

Hello,

How do you get excel to ignore certain data when reading a cell?

I want to use VLOOKUP to match a cell reading "NP001" with "N001"

Or even better I want to use it in a SumProduct Formula:

=SUMPRODUCT((A1:A10="N001")*(B1:B10=--"02/06/06");C1:C10)

Thanks,
P

Hi Ross,

maybe this is good for you:

=SUMPRODUCT((SUBSTITUTE(A1:A10,"P","")="N001")*(B1 :B10=--"02/06/06");C1:C10)

Thank you that does work to an extent, however my cell keeps
returning #VALUE as it is text I am trying to return not numbers, any
suggestions on a way round this?



But in your first post you said you want to use SUMPRODUCT.. This function
is used to make calculation, so need almost 1 range with numbers... If you
have only text, we have to change... Try to post an example of the way you
would use VLOOKUP...

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Nel post
*Ross* ha scritto:

Im not really sure what VLOOKUP to use or where to start I want to
return the HDR data from belwo table using a reference cell stating
"S59" and a cell stating the Date "1/4/2006 00:00"

Code ProdDate OnLine Hdr
SP59 01/04/2006 00:00 24 HDR2
SP69 01/04/2006 00:00 24 HDR1
MS19 01/04/2006 00:00 0 TEST
SP28 01/04/2006 00:00 0 SHUT
SP33 01/04/2006 00:00 24 HDR1
SP50 01/04/2006 00:00 24 HDR2

Thanks,

Maybe you can add a first column in which you can concatenate the code and
the date and then use a VLOOKUP funcition like this:

=VLOOKUP(A2&A3,A5:D8,4,FALSE)

where in A2 you put the code (S59) and in A3 the date (1/4/2006 00:00) you
want to search for.

Adjust the references.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy