Is it possible to use something like this formula, amended as required, to
have it display the text from the cell where the first two parts of the
formula are true?
=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)
Or is something totally different needed? Could someone please show what
the formula would need to be?

Rob

Try this array formula** :

=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet!A1:A37 0=B9)*(WorkSheet!C1:C370=C9),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"RobN" wrote in message
...
Is it possible to use something like this formula, amended as required, to
have it display the text from the cell where the first two parts of the
formula are true?
=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)
Or is something totally different needed? Could someone please show what
the formula would need to be?

Rob

Thanks Biff. Worked great!!

I Just added an IfError to get......
=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(Work Sheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0 )),)
Rob

"T. Valko" wrote in message
...
Try this array formula** :

=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet!A1:A37 0=B9)*(WorkSheet!C1:C370=C9),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"RobN" wrote in message
...
Is it possible to use something like this formula, amended as required,
to have it display the text from the cell where the first two parts of
the formula are true?
=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)
Or is something totally different needed? Could someone please show what
the formula would need to be?

Rob

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"RobN" wrote in message
...
Thanks Biff. Worked great!!

I Just added an IfError to get......
=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(Work Sheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0 )),)
Rob

"T. Valko" wrote in message
...
Try this array formula** :

=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet!A1:A37 0=B9)*(WorkSheet!C1:C370=C9),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"RobN" wrote in message
...
Is it possible to use something like this formula, amended as required,
to have it display the text from the cell where the first two parts of
the formula are true?
=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)
Or is something totally different needed? Could someone please show
what the formula would need to be?

Rob

Biff,

Are you able to explain why this formula is an array formula
=INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0))

AND

why this one is not?
=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I don't really understand arrays so your explanation using these examplesmay help.Rob"T. Valko" wrote in .. . You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN" wrote in ... Thanks Biff. Worked great!! I Just added an IfError to get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),) Rob "T. Valko" wrote in . .. Try this array formula** :=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0)) ** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "RobN" wrote in . .. Is it possible to use something like this formula, amended as required,to have it display the text from the cell where the first two parts of theformula are true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370) Or is something totally different needed? Could someone please showwhat the formula would need to be? Rob

Technically, *both* formulas are formulas that work with (manipulate)
arrays. The difference is that the INDEX formula needs to be array entered
(CTRL, SHIFT, ENTER) while the SUMPRODUCT formula does not.

The programmer that developed the SUMPRODUCT function wrote this array
processing functionality directly into the function code. The INDEX formula
could also be written in such a way that it does not need to be array
entered:

=INDEX(rng1,MATCH(1,INDEX((rng2="x")*(rng3="y"),,1 ),0))

See if this helps:

http://www.cpearson.com/Excel/ArrayFormulas.aspx



--
Biff
Microsoft Excel MVP


"RobN" wrote in message
...
Biff,

Are you able to explain why this formula is an array formula
=INDEX(WorkSheet!$V$1:$V$370,MATCH(1,(WorkSheet!$A $1:$A$370=B9)*(WorkSheet!$C$1:$C$370=C9),0))

AND

why this one is not?
=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)I
don't really understand arrays so your explanation using these examplesmay
help.Rob"T. Valko" wrote in
.. . You're welcome.
Thanks for the feedback! -- Biff Microsoft Excel MVP "RobN"
wrote in
... Thanks Biff.
Worked great!! I Just added an IfError to
get......=IFERROR(INDEX(WorkSheet!$V$1:$V$370,MA TCH(1,(WorkSheet!$A$1:$A$370=B9)*(WorkSheet!$C$1:$ C$370=C9),0)),)
Rob "T. Valko" wrote in
. .. Try this array
formula**
:=INDEX(WorkSheet!P1:P370,MATCH(1,(WorkSheet !A1:A370=B9)*(WorkSheet!C1:C370=C9),0))
** array formulas need to be entered using the key combination
ofCTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel
MVP "RobN" wrote in
. .. Is it possible to
use something like this formula, amended as required,to have it display
the text from the cell where the first two parts of theformula are
true?=SUMPRODUCT(--(WorkSheet!$A$1:$A$370=$B9),--(WorkSheet!$C$1:$C$370=$C9),WorkSheet!P$1:P$370)
Or is something totally different needed? Could someone please showwhat
the formula would need to be? Rob