Hi,

I have an Excel question and I'm not sure if I'm approaching it
correctly. I have a column of data (C7:C8398) which is all text and I
need to return the value given 3 conditions so I wrote the following
array.

=IF((A7:A8398=P7)*(K7:K8398=Q7)*(M7:M8398=0),C7:C 8398,FALSE)

The value is a unique data point (e.g., there is only 1 data point in
the entire column that satisfies all 3 conditions). For example, let's
say the answer is "test". I don't know how to retrieve the value that
I want because the result of the array would be: [FALSE, FALSE, FALSE,
...., "test", FALSE, FALSE...]

I tried using INDEX/MATCH functions but I think that requires me to
know what the answer is. I don't know the answer is "test". I have a
whole column of answers (e.g., C7:C8398) but I don't know the answer
that will satisfy the conditions.

Any ideas? Am I approaching this incorrectly?

Thanks in advance,
Stuart

=SUMPRODUCT((A7:A8398=P7)*(K7:K8398=Q7)*(M7:M8398 =0),C7:C8398)

You were on the right direction!

HTH
--
AP

a écrit dans le message de news:
...
Hi,

I have an Excel question and I'm not sure if I'm approaching it
correctly. I have a column of data (C7:C8398) which is all text and I
need to return the value given 3 conditions so I wrote the following
array.

=IF((A7:A8398=P7)*(K7:K8398=Q7)*(M7:M8398=0),C7:C 8398,FALSE)

The value is a unique data point (e.g., there is only 1 data point in
the entire column that satisfies all 3 conditions). For example, let's
say the answer is "test". I don't know how to retrieve the value that
I want because the result of the array would be: [FALSE, FALSE, FALSE,
..., "test", FALSE, FALSE...]

I tried using INDEX/MATCH functions but I think that requires me to
know what the answer is. I don't know the answer is "test". I have a
whole column of answers (e.g., C7:C8398) but I don't know the answer
that will satisfy the conditions.

Any ideas? Am I approaching this incorrectly?

Thanks in advance,
Stuart

Try also, array-entered (press CTRL+SHIFT+ENTER):
=INDEX(C7:C8398,MATCH(1,(A7:A8398=P7)*(K7:K8398=Q7 )*(M7:M8398=0),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:

Hi,

I have an Excel question and I'm not sure if I'm approaching it
correctly. I have a column of data (C7:C8398) which is all text and I
need to return the value given 3 conditions so I wrote the following
array.

=IF((A7:A8398=P7)*(K7:K8398=Q7)*(M7:M8398=0),C7:C 8398,FALSE)

The value is a unique data point (e.g., there is only 1 data point in
the entire column that satisfies all 3 conditions). For example, let's
say the answer is "test". I don't know how to retrieve the value that
I want because the result of the array would be: [FALSE, FALSE, FALSE,
...., "test", FALSE, FALSE...]

I tried using INDEX/MATCH functions but I think that requires me to
know what the answer is. I don't know the answer is "test". I have a
whole column of answers (e.g., C7:C8398) but I don't know the answer
that will satisfy the conditions.

Any ideas? Am I approaching this incorrectly?

Thanks in advance,
Stuart

Public Function GetAnswer() As String
For i = 7 To 8398
If Sheet1.Range("A" & i).Value = Sheet1.Range("P7").Value Then
If Sheet1.Range("K" & i).Value = Sheet1.Range("Q7").Value Then
If Sheet1.Range("M" & i).Value 0 Then
GetAnswer = Sheet1.Range("C" & i).Value
Exit For
End If
End If
End If
Next i
End Function

This is how to do that

Go to ToolsMacroVisualBasic Editor
then Right click on the VBAProject, select INSERTModule

then paste the code above in the coding area

Back to your excel sheet.

Goto to A1 then on the menu click Insert Functions

On the insert function dialog, in the category select User define
function
and in the function list select GetAnswer

Hope u understand my instruction.

thank you all for your help!