How to return a specific data point from a large array if I don't know the exact location?

[stuart.kwok]

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:C8398,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

 

[Ardus Petus]

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

You were on the right direction!

HTH

 

[Guest]

Try also, array-entered (press CTRL+SHIFT+ENTER):
=INDEX(C7:C8398,MATCH(1,(A7:A8398=P7)*(K7:K8398=Q7)*(M7:M8398>=0),0))

 

[arthurjr07]

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 Tools>Macro>VisualBasic Editor
then Right click on the VBAProject, select INSERT>Module

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.

 

[stuart.kwok]

thank you all for your help!