software

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:M8398gt;=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 quot;testquot;. I don't know how to retrieve the value that
I want because the result of the array would be: [FALSE, FALSE, FALSE,
...., quot;testquot;, 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 quot;testquot;. 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:M8398gt; =0),C7:C8398)

You were on the right direction!

HTH
--
AP

gt; a écrit dans le message de news:
...
gt; Hi,
gt;
gt; I have an Excel question and I'm not sure if I'm approaching it
gt; correctly. I have a column of data (C7:C8398) which is all text and I
gt; need to return the value given 3 conditions so I wrote the following
gt; array.
gt;
gt; =IF((A7:A8398=P7)*(K7:K8398=Q7)*(M7:M8398gt;=0),C7:C 8398,FALSE)
gt;
gt; The value is a unique data point (e.g., there is only 1 data point in
gt; the entire column that satisfies all 3 conditions). For example, let's
gt; say the answer is quot;testquot;. I don't know how to retrieve the value that
gt; I want because the result of the array would be: [FALSE, FALSE, FALSE,
gt; ..., quot;testquot;, FALSE, FALSE...]
gt;
gt; I tried using INDEX/MATCH functions but I think that requires me to
gt; know what the answer is. I don't know the answer is quot;testquot;. I have a
gt; whole column of answers (e.g., C7:C8398) but I don't know the answer
gt; that will satisfy the conditions.
gt;
gt; Any ideas? Am I approaching this incorrectly?
gt;
gt; Thanks in advance,
gt; Stuart
gt;
Try also, array-entered (press CTRL SHIFT ENTER):
=INDEX(C7:C8398,MATCH(1,(A7:A8398=P7)*(K7:K8398=Q7 )*(M7:M8398gt;=0),0))
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot; wrote:

gt; Hi,
gt;
gt; I have an Excel question and I'm not sure if I'm approaching it
gt; correctly. I have a column of data (C7:C8398) which is all text and I
gt; need to return the value given 3 conditions so I wrote the following
gt; array.
gt;
gt; =IF((A7:A8398=P7)*(K7:K8398=Q7)*(M7:M8398gt;=0),C7:C 8398,FALSE)
gt;
gt; The value is a unique data point (e.g., there is only 1 data point in
gt; the entire column that satisfies all 3 conditions). For example, let's
gt; say the answer is quot;testquot;. I don't know how to retrieve the value that
gt; I want because the result of the array would be: [FALSE, FALSE, FALSE,
gt; ...., quot;testquot;, FALSE, FALSE...]
gt;
gt; I tried using INDEX/MATCH functions but I think that requires me to
gt; know what the answer is. I don't know the answer is quot;testquot;. I have a
gt; whole column of answers (e.g., C7:C8398) but I don't know the answer
gt; that will satisfy the conditions.
gt;
gt; Any ideas? Am I approaching this incorrectly?
gt;
gt; Thanks in advance,
gt; Stuart
gt;
gt;

Public Function GetAnswer() As String
For i = 7 To 8398
If Sheet1.Range(quot;Aquot; amp; i).Value = Sheet1.Range(quot;P7quot;).Value Then
If Sheet1.Range(quot;Kquot; amp; i).Value = Sheet1.Range(quot;Q7quot;).Value Then
If Sheet1.Range(quot;Mquot; amp; i).Value gt; 0 Then
GetAnswer = Sheet1.Range(quot;Cquot; amp; i).Value
Exit For
End If
End If
End If
Next i
End Function

This is how to do that

Go to Toolsgt;Macrogt;VisualBasic Editor
then Right click on the VBAProject, select INSERTgt;Module

then paste the code above in the coding area

Back to your excel sheet.

Goto to A1 then on the menu click Insertgt; 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!