Sum product

[HJ]

I'm using the following sumproduct formula to look up two pieces on
information on a spreadsheet and then return the text that is in column A but
keep getting a #value! error. All the formatting is set to general so I'm
not sure what I am doing wrong. Any advice?

=sumproduct(('Tab1'!B2:B1199=A3)*('Tab1'!C2:C1199=B3)*('Tab1'!A2:A1199))

[Kevin Vaughn]

The problem is sumproduct sums numbers it does not return text.
--
Kevin Vaughn


"HJ" wrote:

> I'm using the following sumproduct formula to look up two pieces on
> information on a spreadsheet and then return the text that is in column A but
> keep getting a #value! error. All the formatting is set to general so I'm
> not sure what I am doing wrong. Any advice?
>
> =sumproduct(('Tab1'!B2:B1199=A3)*('Tab1'!C2:C1199=B3)*('Tab1'!A2:A1199))
>
>

[HJ]

Is there another function I can use to look up two pieces of information and
then return text that is in another column?

"Kevin Vaughn" wrote:

> The problem is sumproduct sums numbers it does not return text.
> --
> Kevin Vaughn
>
>
> "HJ" wrote:
>
> > I'm using the following sumproduct formula to look up two pieces on
> > information on a spreadsheet and then return the text that is in column A but
> > keep getting a #value! error. All the formatting is set to general so I'm
> > not sure what I am doing wrong. Any advice?
> >
> > =sumproduct(('Tab1'!B2:B1199=A3)*('Tab1'!C2:C1199=B3)*('Tab1'!A2:A1199))
> >
> >

[Bob Phillips]

=INDEX('Tab1'!A2:A1199,MATCH(1,('Tab1'!B2:B1199=A3)*('Tab1'!C2:C1199=B3),0)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"HJ" <HJ@discussions.microsoft.com> wrote in message
news:58153D21-BBC9-4480-BEA1-E1C520A56B0E@microsoft.com...
> Is there another function I can use to look up two pieces of information
and
> then return text that is in another column?
>
> "Kevin Vaughn" wrote:
>
> > The problem is sumproduct sums numbers it does not return text.
> > --
> > Kevin Vaughn
> >
> >
> > "HJ" wrote:
> >
> > > I'm using the following sumproduct formula to look up two pieces on
> > > information on a spreadsheet and then return the text that is in
column A but
> > > keep getting a #value! error. All the formatting is set to general so
I'm
> > > not sure what I am doing wrong. Any advice?
> > >
> > >
=sumproduct(('Tab1'!B2:B1199=A3)*('Tab1'!C2:C1199=B3)*('Tab1'!A2:A1199))
> > >
> > >

[Domenic]

Try the following formula instead...

=INDEX(Tab1!A2:A1199,MATCH(1,(Tab1!B2:B1199=A3)*(Tab1!C2:C1199=B3),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <F6929BD4-E2B0-4D52-AEBC-C9D63F7F6BED@microsoft.com>,
"HJ" <HJ@discussions.microsoft.com> wrote:

> I'm using the following sumproduct formula to look up two pieces on
> information on a spreadsheet and then return the text that is in column A but
> keep getting a #value! error. All the formatting is set to general so I'm
> not sure what I am doing wrong. Any advice?
>
> =sumproduct(('Tab1'!B2:B1199=A3)*('Tab1'!C2:C1199=B3)*('Tab1'!A2:A1199))

[HJ]

Thank you. I'm still getting a NAME? error. Any thoughts on that one?

"Domenic" wrote:

> Try the following formula instead...
>
> =INDEX(Tab1!A2:A1199,MATCH(1,(Tab1!B2:B1199=A3)*(Tab1!C2:C1199=B3),0))
>
> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>
> Hope this helps!
>
> In article <F6929BD4-E2B0-4D52-AEBC-C9D63F7F6BED@microsoft.com>,
> "HJ" <HJ@discussions.microsoft.com> wrote:
>
> > I'm using the following sumproduct formula to look up two pieces on
> > information on a spreadsheet and then return the text that is in column A but
> > keep getting a #value! error. All the formatting is set to general so I'm
> > not sure what I am doing wrong. Any advice?
> >
> > =sumproduct(('Tab1'!B2:B1199=A3)*('Tab1'!C2:C1199=B3)*('Tab1'!A2:A1199))
>

[HJ]

Never mind. I figured out what I was doing wrong. Thanks so much Domenic
for the formula, it works great!!!!

"HJ" wrote:

> Thank you. I'm still getting a NAME? error. Any thoughts on that one?
>
> "Domenic" wrote:
>
> > Try the following formula instead...
> >
> > =INDEX(Tab1!A2:A1199,MATCH(1,(Tab1!B2:B1199=A3)*(Tab1!C2:C1199=B3),0))
> >
> > ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
> >
> > Hope this helps!
> >
> > In article <F6929BD4-E2B0-4D52-AEBC-C9D63F7F6BED@microsoft.com>,
> > "HJ" <HJ@discussions.microsoft.com> wrote:
> >
> > > I'm using the following sumproduct formula to look up two pieces on
> > > information on a spreadsheet and then return the text that is in column A but
> > > keep getting a #value! error. All the formatting is set to general so I'm
> > > not sure what I am doing wrong. Any advice?
> > >
> > > =sumproduct(('Tab1'!B2:B1199=A3)*('Tab1'!C2:C1199=B3)*('Tab1'!A2:A1199))
> >