ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   How to return a specific data point from a large array if I don't know the exact location? (https://www.excelbanter.com/excel-discussion-misc-queries/86296-how-return-specific-data-point-large-array-if-i-dont-know-exact-location.html)

[email protected]

How to return a specific data point from a large array if I don't know the exact location?
 
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


Ardus Petus

How to return a specific data point from a large array if I don't know the exact location?
 
=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




Max

How to return a specific data point from a large array if I don't
 
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



[email protected]

How to return a specific data point from a large array if I don't know the exact location?
 
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.


[email protected]

How to return a specific data point from a large array if I don't know the exact location?
 
thank you all for your help!



All times are GMT +1. The time now is 11:47 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com